MySQL 日期函数、时间函数在实际场景中的应用 - 酸菜鱼土豆大侠
source link: https://www.cnblogs.com/chengyj/p/17065699.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
整理日常业务中用到日期函数的一些场景,并对日期函数按照使用类型做了分类,实例也尽可能符合日常需求。为了方便查阅,可以先看目录,再根据需要看具体方法和实例。
首先明确日期和时间类型有哪些,也就是日期函数括号中的变量类型。
一、日期和时间类型
表示时间值的日期和时间类型为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');
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK