pgSQL,时间类型timestamp结合mybatis小记

最新推荐文章于 2025-02-14 23:58:01 发布

原创最新推荐文章于 2025-02-14 23:58:01 发布 · 6.4k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#sql

sql 专栏收录该内容

2 篇文章

订阅专栏

本文探讨了在SpringBoot与MyBatis框架下，如何优化基于时间的数据库查询，包括精确到天的单日查询和日期区间查询，通过SQL语句的巧妙设计，实现了高效的数据检索。

背景:项目是用springboot+mybatis重构的,原先是用spring.数据库使用的是pgSQL一开始就统一时间参数接收和数据库都使用timestamp类型,后期带来了不算太大的麻烦

需求一,查询所有的项目表数据,查询条件有具体某一天新建的项目

SELECT

p_id,p_name,create_time from project

WHERE

1=1

<if test="createTime != null and createTime != '' ">
AND to_char(create_time, 'YYYY-MM-DD') = to_char(to_date(#{createTime},'YYYY-MM-DD'), 'YYYY-MM-DD')
</if>

上面是查询出哪些字段,下面的if标签是mybatis判断,如果时间没有给就不执行if标签里的sql语句,

createTime是传递进来的参数,create_time是数据库字段数据,

timestamp是时间戳,一般长这样1564651689000,java底层是字符串.

首先to_date(#{createTime},'YYYY-MM-DD') 我要传递进来的时间参数转换成date类型

to_char(to_date(#{createTime},'YYYY-MM-DD'), 'YYYY-MM-DD') 再然后把它转换成字符类型'YYYY-MM-DD'格式

最后一步就是把数据库的字段转换成字符类型和传递进来的参数字符类型比较.

to_char(create_time, 'YYYY-MM-DD') = to_char(to_date(#{createTime},'YYYY-MM-DD'), 'YYYY-MM-DD')

总结:其实思路你也应该能明白了.我把参数时间转换成时间格式的Date,再按照一个格式转换成字符.再把数据库的时间转换成字符进行比较,数据库的字符比较不就是'='嘛!

上面的问题解决了,那么又一个新需求来了.产品狗说我不想要某一天的了,我想要一个区间内的,精确到 /天,心里一万只草拟吗

需求二,查询所有的项目表数据,查询条件有具体某一天到某一天的区间内项目

SELECT

p_id,p_name,create_time from project

WHERE

1=1

<if test=" startTime != null and endTime != null ">
    AND to_date(to_char(create_time, 'YYYY-MM-DD') , 'YYYY-MM-DD')
    BETWEEN
    to_date(#{startTime}, 'YYYY-MM-DD' )
    AND
    to_date(#{endTime}, 'YYYY-MM-DD' )
</if>

好,这里我们就不废话了,和上面一样.但是这是次传各位2个参数,开始时间和结束时间.

思考:上面第一个需求是查某一天,我格式化成字符比较.那么现在区间我再格式化成字符用=来比较完全不对的.那只能在这两个时间的区间内.如果是数字用>/<比较.其实上面已经有答案了,直接比较时间.

我先把传递进来的2个时间转换成固定格式的date,用 BETWEEN ... AND来做(在.....之间)翻译过来就是,在开始时间和结束时间区间