mybatis-plus时间段查询不出来解决方法(亲测有效)

泪在飘

已于 2022-06-28 23:41:52 修改

阅读量2.2k

点赞数 2

分类专栏： JAVA 文章标签： mybatis mysql sql

于 2022-06-28 23:39:27 首次发布

本文链接：https://blog.csdn.net/weixin_44046384/article/details/125511914

版权

JAVA 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

控制台Mybatis-Plus日志

queryWrapper错误写法（有时候生效有时候失效）：

==> Preparing: SELECT XXXX  FROM REALNAME_DEALER WHERE ( create_time <= ? AND create_time >= ?)
==> Parameters: 2022-06-28 23:20:10.548(Timestamp), 2022-06-27 23:20:10.548(Timestamp)
<== Total: 0

正确写法 create_time为Mysql数据库中类型为timestamp的字段

==> Preparing: SELECT XXXX  FROM REALNAME_DEALER WHERE (UNIX_TIMESTAMP(create_time) >= UNIX_TIMESTAMP('2022-06-27 23:20:10') AND UNIX_TIMESTAMP(create_time) <= UNIX_TIMESTAMP('2022-06-28 23:20:10'))
==> Parameters: 0(Integer), 3(Integer)
<== Columns: id, channel_number, channel_name, channel_level, address, phone, operator, err_msg, sync_status, create_time, update_time
<== Row: 4,xxxx , 2022-06-28 17:38:22, null
<== Row: 5,xxx
<== Row: 6,xxx
<== Row: 7,xxx
<== Total: 4

业务

查询24小时内的数据

代码

start和end为 java中的Date

        Date end = new Date();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(end);
        calendar.add(Calendar.DATE, -1);
        Date start = calendar.getTime();

queryWrapper错误写法（有时候生效有时候失效）：

    queryWrapper.ge(DeaVehRelationship::getCreateTime, start)
                .le(DeaVehRelationship::getCreateTime, end);

正确写法 create_time为Mysql数据库中类型为timestamp的字段

        String strStart= DateFormatUtils.format(start,"yyyy-MM-dd HH:mm:ss");
        String strEnd= DateFormatUtils.format(end,"yyyy-MM-dd HH:mm:ss");
        queryWrapper.apply("UNIX_TIMESTAMP(create_time) >= UNIX_TIMESTAMP('" + strStart + "')");
        queryWrapper.apply("UNIX_TIMESTAMP(create_time) <= UNIX_TIMESTAMP('" + strEnd + "')");