java開發(fā)之SQL語句中DATE_FORMAT函數舉例詳解

更新時間：2024年05月22日 09:29:47 作者：量子矩陣是實現觸碰四維空間基本算法

要將日期值格式化為特定格式,請使用DATE_FORMAT函數,下面這篇文章主要給大家介紹了關于java開發(fā)之SQL語句中DATE_FORMAT函數的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

引言：

實際上在使用Java開發(fā)過程中，有很多業(yè)務場景下，都有時間類型的參數參與。前后端進行交互的時候，針對時間類型的格式都會做一個業(yè)務上的統一，方便開發(fā)且增加效率。關于后端的邏輯有兩個層面可以進行優(yōu)化，一個是底層sql方面，一個是業(yè)務層方面，這兩者之間的處理都可以影響整個業(yè)務方法的響應時間。本次分享在sql層面使用DATE_FORMAT 函數對時間類型數據的處理

1、簡單介紹：

DATE_FORMAT 是 SQL 中的一個函數，用于將日期或時間格式化為指定的格式，以下是sql形式的格式

DATE_FORMAT(date, format)

其中：

date 是要格式化的日期或時間。
format 是你希望得到的輸出格式。

2、舉例

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 一樣。注意：需要添加時區(qū)(timezone)

重點是無論怎么丟參數，丟的都是Date類型，現在使用sql 中 DATE_FORMAT 函數來參與業(yè)務

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函數的簡單使用。

附：計算輸入的日期的差值

SimpleDateFormat date = new SimpleDateFormat("yyyy年MM月dd日");
? ? ? ? Date date1 = new Date();

? ? ? ? Date date2 = date.parse("2020年1月1日");
? ? ? ? System.out.println((date1.getTime()-date2.getTime())/1000/3600/24+"天");

結果：我目前的時間距離2020年1月1日有223天。