【Java】【SQL】sql中 DATE_FORMAT函数详解

在实际应用开发中，使用sql语句也属于开发者的一部分，这次来说说DATE_FORMAT函数。

引言：实际上在使用Java开发过程中，有很多业务场景下，都有时间类型的参数参与。前后端进行交互的时候，针对时间类型的格式都会做一个业务上的统一，方便开发且增加效率。关于后端的逻辑有两个层面可以进行优化，一个是底层sql方面，一个是业务层方面，这两者之间的处理都可以影响整个业务方法的响应时间。本次分享在sql层面使用DATE_FORMAT 函数 对时间类型数据的处理

1、简单介绍：DATE_FORMAT 是 SQL 中的一个函数，用于将日期或时间格式化为指定的格式，以下是sql形式的格式

DATE_FORMAT(date, format)

其中：
date 是要格式化的日期或时间。
format 是你希望得到的输出格式。

举例

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

常见的格式化选项有：

%Y 年份，四位数
%y 年份，两位数
%m 月份，两位数
%d 日期，两位数
%H 小时，24小时制，两位数
%h 小时，12小时制，两位数
%i 分钟，两位数
%s 秒，两位数
%p AM/PM

例子中，把时间转化为 年月日格式，和电脑右下角的日期是相同的，那么针对Java中的传参怎么应用呢，以下：

@Data
public class AITimeDatePlay {

	@DateTimeFormat(
			pattern = "yyyy-MM-dd HH:mm:ss"
	)
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
	private Date testTime;
}

代码中仅有一个字段，该字段上贴有2个注解，插个题外话，大概解释一下：

@DateTimeFormat ：当前端丢给后端时间参数的时候，这个注解就将参数中的时间类型的样式转为我们设置的在该注解中的pattern一样。

@JsonFormat：当后端丢给前端的结果是json模式时(一般都是json)，该注解会把后端结果中的时间类型参数转为我们在该注解中设置的pattern 一样。注意：需要添加时区(timezone)

重点是无论怎么丢参数，丢的都是Date类型，现在 使用sql 中 DATE_FORMAT 函数来参与业务

List<EmployeeInfo> queryInfomationByTime(AITimeDatePlay dto);

最终查询sql

<select id="queryInfomationByTime" resultType="com.bonade.hrm.foundation.model.EmployeeInfo">
        select *
        from employee_info ei
        where date_format(ei.initiation_time,'%Y-%m-%d') = DATE_FORMAT(#{testTime},'%Y-%m-%d')
    </select>

也可以当作字段返回，如下：

select date_format(ei.initiation_time,'%Y-%m-%d') as bigTime
        from employee_info ei
        where date_format(ei.initiation_time,'%Y-%m-%d') = DATE_FORMAT(#{testTime},'%Y-%m-%d')

也可以按照需求，只精确到年月

select date_format(ei.initiation_time,'%Y-%m') as bigTime
        from employee_info ei
        where date_format(ei.initiation_time,'%Y-%m') = DATE_FORMAT(#{testTime},'%Y-%m')

也可以输入首、尾时间，取数据库中某个时间单位是否在输入的时间段之中

select date_format(ei.initiation_time,'%Y-%m') as bigTime
        from employee_info ei
        where (
            ((DATE_FORMAT( ei.initiation_time, '%Y-%m' )) BETWEEN  #{startTime} AND #{endTime})
            OR (( DATE_FORMAT( ei.leave_time, '%Y-%m' )) BETWEEN  #{startTime} AND #{endTime})
            )

startTime 为传入的开始时间参数，endTime 为传入的结束时间参数，这二者之间的时间段可以作为一个筛选条件，若该二者时间格式也需要调整，那么如下：

select date_format(ei.initiation_time,'%Y-%m') as bigTime
        from employee_info ei
        where (
            ((DATE_FORMAT( ei.initiation_time, '%Y-%m' )) BETWEEN  DATE_FORMAT(#{startTime},'%Y-%m') AND DATE_FORMAT(#{endTime},'%Y-%m'))
            OR (( DATE_FORMAT( ei.leave_time, '%Y-%m' )) BETWEEN  DATE_FORMAT(#{startTime},'%Y-%m') AND DATE_FORMAT(#{endTime},'%Y-%m'))
            )

以上则是分享的DATE_FORMAT函数的简单使用。