整理日常业务中用到日期函数的一些场景，并对日期函数按照使用类型做了分类，实例也尽可能符合日常需求。为了方便查阅，可以先看目录，再根据需要看具体方法和实例。
首先明确日期和时间类型有哪些，也就是日期函数括号中的变量类型。
一、日期和时间类型
表示时间值的日期和时间类型为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）获取年、月、日、周等
| 函数 | 描述 |
|---|---|
| date(datetime) | 返回datetime中的日期date部分 |
| 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 的子字符串 |
| 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只能求日期的天数差。
（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 date('2023-01-09 22:32:17'); -- 2023-01-09 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_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 表 (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'); 
