MySQL 日期函式、時間函式在實際場景中的應用

酸菜魚土豆大俠發表於2023-01-23

整理日常業務中用到日期函式的一些場景，並對日期函式按照使用型別做了分類，例項也儘可能符合日常需求。為了方便查閱，可以先看目錄，再根據需要看具體方法和例項。
首先明確日期和時間型別有哪些，也就是日期函式括號中的變數型別。

一、日期和時間型別

表示時間值的日期和時間型別為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

型別	格式	描述
DATE	YYYY-MM-DD	日期值
TIME	HH:MM:SS	時間值或持續時間
YEAR	YYYY	年份值
DATETIME	YYYY-MM-DD hh:mm:ss	混合日期和時間值
TIMESTAMP	YYYY-MM-DD hh:mm:ss	混合日期和時間值，時間戳

二、具體方法

（1）獲取年、月、日、周等

函式	描述
year(date)	返回年份
quarter(date)	返回日期date是第幾季節，返回 1 到 4
month(date)	返回日期date中的月份值，1 到 12
week(date)	計算日期 date 是本年的第幾個星期，範圍是 0 到 53
day(date)	返回日期值 date 的日期部分
hour(time)	返回 date 中的小時值
minute(time)	返回 date 中的分鐘值
second(time)	返回 t 中的秒鐘值
microsecond(time)	返回日期引數所對應的微秒數
extract(type from date)	從日期 date 中獲取指定的值，type 指定返回的值
weekday(date)	日期 date 是星期幾，0 表示星期一，1 表示星期二...

本文中type的取值：

year(年)、quarter(季)、month(月)、week(周)、day(天)、hour(小時)、minute(分鐘)、second(秒)、microsecond(毫秒)。

（2）獲取年月、年月日等

函式	描述
year、month、day	使用and連線單個返回值
date_format(event_time,’unit‘)	按照unit取值
substring(date, start, length)	從字串 str 的 start 位置開始擷取長度為 length 的子字串
date(datetime)	返回datetime中的日期date部分
like	模糊查詢

本文中unit的取值：

%M 月名字(January……December) 
%W 星期名字(Sunday……Saturday) 
%D 有英語字首的月份的日期(1st, 2nd, 3rd, 等等。） 
%Y 年, 數字, 4 位 
%y 年, 數字, 2 位 
%a 縮寫的星期名字(Sun……Sat) 
%d 月份中的天數, 數字(00……31) 
%e 月份中的天數, 數字(0……31) 
%m 月, 數字(01……12) 
%c 月, 數字(1……12) 
%b 縮寫的月份名字(Jan……Dec) 
%j 一年中的天數(001……366) 
%H 小時(00……23) 
%k 小時(0……23) 
%h 小時(01……12) 
%I 小時(01……12) 
%l 小時(1……12) 
%i 分鐘, 數字(00……59) 
%r 時間,12 小時(hh:mm:ss [AP]M) 
%T 時間,24 小時(hh:mm:ss) 
%S 秒(00……59) 
%s 秒(00……59) 
%p AM或PM 
%w 一個星期中的天數(0=Sunday ……6=Saturday ） 
%U 星期(0……52), 這裡星期天是星期的第一天 
%u 星期(0……52), 這裡星期一是星期的第一天 
%% 一個文字“%”。

例如：date_format(%Y-%m-%d %H:%i:%s)得到時間戳

（3）兩個日期時間的整數差

函式	描述
datediff(date_1,date_2)	返回日期 date_1 - date_2 之間相隔的天數
timestampdiff(type,datetime_1,datetime_2)	返回 datetime_2 − datetime_1 的時間差
timediff(time_1, time_2)計算時間差值	返回time_1 - time_2 的時間差值

引數說明：

type：引數確定（end-begin）結果的單位，表示為整數。可以填year(年)、quarter(季)、month(月)、week(周)、day(天)、hour(小時)、minute(分鐘)、second(秒)、microsecond(毫秒)。

返回datetime_2-datetime_1的結果，其中datetime_1和datetime_2是date或者datetime表示式，也可以是混合型別；

當datetime_1>datetime_2時，返回的結果為負；

當datetime_1<datetime_2時，返回的結果為正。
- 什麼時候使用datediff？什麼時候使用timestampdiff？什麼時候使用timediff？
三個函式的區別主要在於物件的不同一個是日期，一個是時間，另外一個既可以是日期格式也可以是時間格式。
timestampdiff更加靈活，既可以對日期求整數差也可以對時間求整數差。而datediff只能求日期的天數差;timediff只能求時間的秒數差或者時間差。

（4）日期減去/加上天數得到日期 date_sub、date_add

函式	描述
date_add(date,interval expr type)	返回起始日期 date 加上一個時間段後的日期
date_sub(date,interval expr type)	返回函式從日期減去指定的時間間隔
addtime(time,n)	返回n 是一個時間表示式，時間 t 加上時間表示式 n
subtime(time,n)	時間 t 減去 n 秒的時間

（5）日期date是周幾

函式	描述
weekday(date)	日期 date 是星期幾，0 表示星期一，1 表示星期二...
date_format(date,'%W')	日期 date 是星期幾，Monday週一,Tuesday週二...
week(date)	計算日期 date 是本年的第幾個星期，範圍是 0 到 53

（6）某個月的最後一天

函式	描述
last_day(date)	返回給定日期的那一月份的最後一天

（7）返回當前日期/時間

函式	描述
curdate()、current_date()	返回當前日期
curtime()、current_time()	返回當前時間
now()	返回當前日期和時間

三、例項

分為兩部分，簡單和複雜例項，簡單例項是為了便於理解函式的使用，複雜例項是結合業務場景給出了常見的問題。

1、簡單例項

獲取年、月、日、周等

# 返回'2023-01-09 22:32:17'的 年、月、日等
select
    year('2023-01-09 22:32:17');			          -- 2023
select
    quarter('2023-01-09 22:32:17');			          -- 1
select
    month('2023-01-09 22:32:17');			          -- 1
select
    week('2023-01-09 22:32:17');			          -- 2（本年第二週）
select
    day('2023-01-09 22:32:17');			                  -- 9
select
    hour('2023-01-09 22:32:17');			          -- 22
select
    minute('2023-01-09 22:32:17');			          -- 32
select
    second('2023-01-09 22:32:17');			          -- 17
select
    extract(day from '2023-01-09 22:32:17');                      -- 9
select
    weekday('2023-01-09 22:32:17');			          -- 0（週一）

獲取年月、年月日等

# 返回'2023-01-09 22:32:17'的 年月、年月日等
select
    date('2023-01-09 22:32:17');			  	          -- 2023-01-09
select 
    date_format('2023-01-09 22:32:17','%Y%m');				  -- 202301
select 
    date_format('2023-01-09 22:32:17','%y%m');				  -- 2301
select 
    date_format('2023-01-09 22:32:17','%Y-%m-%d');			  -- 2023-01-09
select 
    date_format('2023-01-09 22:32:17','%H:%i');				  -- 22:32
select 
    date_format('2023-01-09 22:32:17','%s');				  -- 17
select 
    substring('2023-01-09 22:32:17',1,7);				  -- 2023-01
select 
    substring('2023-01-09 22:32:17',1,10);				  -- 2023-01-09
select 
    substring('2023-01-09 22:32:17',12,5);				  -- 22:32
select 
    substring('2023-01-09 22:32:17',18,2);				  -- 17

兩個日期時間的整數差/和

# 返回'2023-01-09 22:32:17'和 '2023-03-15 04:18:56'的 整數差或者和
select 
    datediff('2023-01-09 22:32:17','2023-03-15 04:18:56');		  -- -65
select 
    datediff('2023-03-15 04:18:56','2023-01-09 22:32:17');		  -- 65
	
select 
    timediff('2023-01-09 22:32:17','2023-03-15 04:18:56');		  -- -838:59:59
select 
    timediff('2023-03-15 04:18:56','2023-01-09 22:32:17');		  -- 838:59:59
	
select 
    timestampdiff(year,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 0
select 
    timestampdiff(year,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- 0
	
select 
    timestampdiff(quarter,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 0
select 
    timestampdiff(quarter,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- 0
	
select 
    timestampdiff(month,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 2
select 
    timestampdiff(month,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- -2
	
select 
    timestampdiff(week,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 9
select 
    timestampdiff(week,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- -9
	
select 
    timestampdiff(day,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 64
select 
    timestampdiff(day,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- -64
	
select 
    timestampdiff(hour,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 1541
select 
    timestampdiff(hour,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- -1541
	
select 
    timestampdiff(minute,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 92506
select 
    timestampdiff(minute,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- -92506
	
select 
    timestampdiff(second,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 5550399
select 
    timestampdiff(second,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- -5550399

日期減去/加上天數得到日期 date_sub、date_add

# 返回日期'2023-01-09 22:32:17'加上或者減去 某個時間段 的日期
select 
    date_add('2023-01-09 22:32:17',interval 2 year);			  -- 2025-01-09 22:32:17
select 
    date_add('2023-01-09 22:32:17',interval 2 quarter);			  -- 2023-07-09 22:32:17
select 
    date_add('2023-01-09 22:32:17',interval 2 month);		  	  -- 2023-03-09 22:32:17
select 
    date_add('2023-01-09 22:32:17',interval 2 week);		  	  -- 2023-01-23 22:32:17
select 
    date_add('2023-01-09 22:32:17',interval 2 day);			  -- 2023-01-11 22:32:17
select 
    date_add('2023-01-09 22:32:17',interval 2 hour);			  -- 2023-01-10 00:32:17
select 
    date_add('2023-01-09 22:32:17',interval 2 second);			  -- 2023-01-09 22:32:19
select 
    date_add('2023-01-09 22:32:17',interval 2 minute);			  -- 2023-01-09 22:34:17

select 
    date_sub('2023-01-09 22:32:17',interval 2 year);			  -- 2025-01-09 22:32:17
select 
    date_sub('2023-01-09 22:32:17',interval 2 quarter);			  -- 2023-07-09 22:32:17
select 
    date_sub('2023-01-09 22:32:17',interval 2 month);		  	  -- 2023-03-09 22:32:17
select 
    date_sub('2023-01-09 22:32:17',interval 2 week);		  	  -- 2023-01-23 22:32:17
select 
    date_sub('2023-01-09 22:32:17',interval 2 day);			  -- 2023-01-11 22:32:17
select 
    date_sub('2023-01-09 22:32:17',interval 2 hour);			  -- 2023-01-10 00:32:17
select 
    date_sub('2023-01-09 22:32:17',interval 2 second);			  -- 2023-01-09 22:32:19
select 
    date_sub('2023-01-09 22:32:17',interval 2 minute);			  -- 2023-01-09 22:34:17
# 時間格式預設秒
select 
    addtime('2023-01-09 22:32:17','22:12:00');			  	  -- 2023-01-10 20:44:17
select 
    addtime('2023-01-09 22:32:17','22:12');			  	  -- 2023-01-10 20:44:17
select 
    addtime('2023-01-09 22:32:17','22');			  	  -- 2023-01-09 22:32:39
	
select 
    subtime('2023-01-09 22:32:17','22:12:00');			  	  -- 2023-01-09 00:20:17
select 
    subtime('2023-01-09 22:32:17','22:12');			  	  -- 2023-01-09 00:20:17
select 
    subtime('2023-01-09 22:32:17','22');			  	  -- 2023-01-09 22:31:55

日期date是周幾

# 返回日期'2023-01-09 22:32:17' 是周幾
select 
    weekday('2023-01-09 22:32:17');			  		  -- 0（週一）
select 
    week('2023-01-09 22:32:17');			  		  -- 2（本年第二週）
select 
    date_format('2023-01-09 22:32:17','%W');			  	  -- Monday（週一的英文）

某個月的最後一天

# 返回日期'2023-01-09 22:32:17' 該月最後一天
select 
    last_day('2023-01-09 22:32:17');			  		  -- 2023-01-31

返回當前日期/時間

# 返回當前日期或者時間
select 
    curdate(),			  				  	  -- 2023-01-23
    current_date(),			  				  -- 2023-01-23
    curtime(),			  				  	  -- 23:32:37
    current_time(),			  				  -- 23:32:37
    now();  								  -- 2023-01-23 23:32:37

2、複雜例項

students_score 表 (id表示主鍵，student_id是學號，score是平均成績，date是作答日期)

id	student_id	score	answer_date
1	1123	98	2023-01-08
2	2341	74	2022-02-12
3	5623	66	2022-12-23
4	4386	80	2023-01-12

（1）統計每個月12號作答的次數——month、day

#使用month、day
select 
    month(answer_date) as month,
    count(answer_date) as count
from students_score
where day(answer_date) = 12
group by month;

結果

month  count
1	1
2	1

（2）統計2023年1月作答的次數——year、month、day、date_format、substring

#1、使用year、month、day
select 
    count(answer_date) 
from students_score
where month(answer_date) = 1 and year(answer_date) = 2023;

#2、使用date_format
select 
    count(answer_date) 
from students_score
where date_format(answer_date,'%Y%m') = '202301';

#3、使用substring
select 
    count(answer_date) 
from students_score
where substring(answer_date,1,7) = '2023-01';

#4、使用like
select 
    count(answer_date) 
from students_score
where answer_date like '2023-01%';

#結果：2

（3）計算2022-02-12是2022年的第幾周——week(date)

select week('2022-02-12'); 
  
#結果：6（表示第六週）

（4）計算2023-01-05是周幾——date_format(date,'%W')

select date_format('2023-01-05','%W');   #Thursday（表示週四）

（5）輸出students_score表中2023年在週一到週五作答的學生學號——weekday(date)

#1、使用weekday
select 
    student_id,
    weekday(answer_date) as week
from students_score
where weekday(answer_date) < 5;

#2、使用date_format(date,'%W')
select 
    student_id,
    date_format(answer_date,'%W') as week
from students_score
where date_format(answer_date,'%W') in ('Monday','Tuesday','Wednesday','Thursday','Friday');

注意兩種方法的區別

結果

student_id  week
5623	    4
4386	    3

5623	  Friday
4386	  Thursday

（6）計算每月平均作答次數——last_day(date)

select 
    date_format(answer_date,'%Y%m') as date_,
    count(answer_date)/day(last_day(answer_date)) as avg_num
from students_score
group by date_;

結果

date_	avg_num
202202	0.0357
202212	0.0323
202301	0.0645

（7）近7天和近30天分別作答題目數量

如何計算近7天和近30天的日期：日期減去天數得到日期；另一種是日期減去日期得到天數。

[使用]：date_sub(date,interval expr type)、datediff(date,dt_max) 、timestampdiff(day,date,dt_max)

#先求出今日是哪一天（最大日期）,往前七天是哪一天，往前30天是哪一天
with 
    main as(
        select
	    max(date(answer_date)) as now_date,
	    date_sub(max(date(answer_date)),interval 6 day) as 7_date,
	    date_sub(max(date(answer_date)),interval 29 day) as 30_date
	from students_score
    )

select 
    sum(if(datediff(answer_date,7_date)) >= 0,1,0) as ans_7_num,
    sum(if(datediff(answer_date,30_date)) >= 0,1,0) as ans_30_num
from students_score, main

結果

ans_7_num   ans_30_num
2		3

建立 students_score 表的程式碼

-- ----------------------------
-- Table structure for students_score
-- ----------------------------
DROP TABLE IF EXISTS `students_score`;
CREATE TABLE `students_score`  (
  `id` int(11) NOT NULL COMMENT '主鍵',
  `student_id` int(11) NOT NULL COMMENT '學號',
  `score` int(11) NULL DEFAULT NULL COMMENT '成績',
  `date` date NOT NULL COMMENT '作答日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of students_score
-- ----------------------------
INSERT INTO `students_score` VALUES (1, 1123, 98, '2023-01-08');
INSERT INTO `students_score` VALUES (2, 2341, 74, '2023-01-12');
INSERT INTO `students_score` VALUES (3, 5623, 66, '2022-12-23');
INSERT INTO `students_score` VALUES (4, 4386, 80, '2023-01-12');

hive時間日期函式及典型場景應用
2019-03-24
Hive函式
MySQL日期時間函式大全
2013-07-09
MySql函式
MySQL 日期時間函式大全
2015-09-25
MySql函式
MySQL 的日期和時間函式
2018-04-16
MySql函式
日期時間函式
2016-10-09
函式
mysql中的時間函式
2016-12-22
MySql函式
MySQL日期和時間函式彙總
2020-12-09
MySql函式
mysql日期和時間函式學習
2015-10-19
MySql函式
探索MySQL高階語句（數學函式、聚合函式、字串函式、日期時間函式）
2020-12-27
MySql函式字串
SPL 的日期時間函式
2019-08-07
函式
Clickhouse 時間日期函式
2024-05-14
函式
javascript 日期時間函式(經典＋完善＋實用)
2016-07-13
JavaScript函式
MySql中時間和日期函式
2014-09-24
MySql函式
ORACLE日期時間函式大全
2014-08-02
Oracle函式
SQLServer時間日期函式速查
2011-03-01
SQLServer函式
MySQL時間函式
2011-05-13
MySql函式
MySQL（四）日期函式 NULL函式字串函式
2024-09-05
MySql函式Null字串
【Mysql 學習】日期函式函式
2011-01-01
MySql函式
C/C++中的日期和時間函式
2012-09-22
C++函式
MySQL 5.7關於日期和時間的函式整理
2017-03-24
MySql函式
Sql Server函式全解(4)：日期和時間函式
2015-05-07
SQLServer函式
Sql Server函式全解(四)日期和時間函式
2015-04-30
SQLServer函式
SQL 10 函式 3 日期時間函式 - 5 計算日期差額
2011-03-08
SQL函式
SQL SERVER 時間和日期函式
2016-05-21
SQLServer函式
使用mysql日期與時間函式輕易搞定日期與時間邏輯
2017-02-18
MySql函式
MySQL函式大全(字串函式，數學函式，日期函式，系統級函式，聚合函式)
2020-11-14
MySql函式字串
常用函式--時間函式
2009-07-06
函式
SQL Server 裡的日期和時間函式
2011-09-27
SQLServer函式
Go基礎-時間和日期函式
2022-02-22
Go函式
Sql Server系列：日期和時間函式
2014-11-30
SQLServer函式
WPS表格日期與時間函式
2010-01-12
函式
函式索引的兩個應用場景示例（下）
2013-07-02
函式索引
mysql 時間相關的函式以及日期和字串互轉
2020-09-30
MySql函式字串
mysql時區與時間函式
2016-02-03
MySql函式
mysql幾個時間函式
2013-07-05
MySql函式
Haskell 中的日期函式
2019-10-23
Haskell函式
mysql 自定義分析函式 least 及日期函式
2009-12-22
MySql函式AST
科裡化函式實現以及應用場景講解
2024-04-21
函式