MySQL日期时间操作函数（全的）-九五小庞

一、MySQL常用日期函数

1.1、获取时间

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 获取当前日期时间
select NOW(); # 2021-04-02 09:25:29
# 获取当前日期
SELECT CURDATE(); # 2021-04-02
# 获取当前时间
SELECT CURTIME();  # 09:26:10
 
# 对于时间2021-04-02 09:25:29，分别获取其年、月、日、时、分、秒
SELECT EXTRACT(YEAR FROM NOW()); # 2021
SELECT EXTRACT(MONTH FROM NOW()); # 4
SELECT EXTRACT(DAY FROM NOW()); # 2
SELECT EXTRACT(HOUR FROM NOW()); # 9
SELECT EXTRACT(MINUTE FROM NOW()); # 25
SELECT EXTRACT(SECOND FROM NOW()); # 29
 
# 或者从日期格式字符串中获取
SELECT EXTRACT(SECOND FROM '2021-04-02 10:37:14.123456');  # 14

1.2、日期增加、减少

?
1
2
3
4
5
6
7
8
9
10
11
12
13
# 时间减少1小时（前一小时）
select date_sub(now(), INTERVAL 1 hour);
 
# 日期增加1天
select date_add(now(), INTERVAL 1 day);
 
# 其他间隔
INTERVAL 1 YEAR
INTERVAL 1 MONTH
INTERVAL 1 DAY
INTERVAL 1 HOUR
INTERVAL 1 MINUTE
INTERVAL 1 SECOND

1.3、日期格式化、字符串转日期

?
1
2
3
4
5
6
# 格式化参考：
select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s');
select DATE_FORMAT(now(),'%Y-%m-%d %H:00:00');
 
#字符串转日期
select str_to_date('2021-04-02 10:37:14', '%Y-%m-%d %H:%i:%s'); # 2021-04-02 10:37:14

二、其他参考函数

以下较全的MySQL日期函数可做参考（原文链接：https://blog.csdn.net/qinshijangshan/article/details/72874667）

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
-- MySQL日期时间处理函数
-- 当前日期：2017-05-12（突然发现今天512，是不是会拉防空警报）
SELECT NOW() FROM DUAL;-- 当前日期时间：2017-05-12 11:41:47
-- 在MySQL里也存在和Oracle里类似的dual虚拟表：官方声明纯粹是为了满足select ... from...这一习惯问题，mysql会忽略对该表的引用。
-- 那么MySQL中就不用DUAL了吧。
SELECT NOW();-- 当前日期时间：2017-05-12 11:41:55
-- 除了 now() 函数能获得当前的日期时间外，MySQL 中还有下面的函数：
SELECT CURRENT_TIMESTAMP();-- 2017-05-15 10:19:31
SELECT CURRENT_TIMESTAMP;-- 2017-05-15 10:19:51
SELECT LOCALTIME();-- 2017-05-15 10:20:00
SELECT LOCALTIME;-- 2017-05-15 10:20:10
SELECT LOCALTIMESTAMP();-- 2017-05-15 10:20:21(v4.0.6)
SELECT LOCALTIMESTAMP;-- 2017-05-15 10:20:30(v4.0.6)
-- 这些日期时间函数，都等同于 now()。鉴于 now() 函数简短易记，建议总是使用 now()来替代上面列出的函数。
  
SELECT SYSDATE();-- 当前日期时间：2017-05-12 11:42:03
-- sysdate() 日期时间函数跟 now() 类似，
-- 不同之处在于：now() 在执行开始时值就得到了;sysdate() 在函数执行时动态得到值。
-- 看下面的例子就明白了：
SELECT NOW(), SLEEP(3), NOW();
SELECT SYSDATE(), SLEEP(3), SYSDATE();
  
  
SELECT CURDATE();-- 当前日期：2017-05-12
SELECT CURRENT_DATE();-- 当前日期：等同于 CURDATE()
SELECT CURRENT_DATE;-- 当前日期：等同于 CURDATE()
  
SELECT CURTIME();-- 当前时间：11:42:47
SELECT CURRENT_TIME();-- 当前时间：等同于 CURTIME()
SELECT CURRENT_TIME;-- 当前时间：等同于 CURTIME()
  
-- 获得当前 UTC 日期时间函数
SELECT UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME()
-- MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()
SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP();-- 2017-05-15 10:32:21 | 2017-05-15 10:32:21
  
  
-- MySQL 日期时间 Extract（选取） 函数
SET @dt = '2017-05-15 10:37:14.123456';
SELECT DATE(@dt);-- 获取日期：2017-05-15
SELECT TIME('2017-05-15 10:37:14.123456');-- 获取时间：10:37:14.123456
SELECT YEAR('2017-05-15 10:37:14.123456');-- 获取年份
SELECT MONTH('2017-05-15 10:37:14.123456');-- 获取月份
SELECT DAY('2017-05-15 10:37:14.123456');-- 获取日
SELECT HOUR('2017-05-15 10:37:14.123456');-- 获取时
SELECT MINUTE('2017-05-15 10:37:14.123456');-- 获取分
SELECT SECOND('2017-05-15 10:37:14.123456');-- 获取秒
SELECT MICROSECOND('2017-05-15 10:37:14.123456');-- 获取毫秒
SELECT QUARTER('2017-05-15 10:37:14.123456');-- 获取季度
SELECT WEEK('2017-05-15 10:37:14.123456');-- 20 (获取周)
SELECT WEEK('2017-05-15 10:37:14.123456', 7);-- ****** 测试此函数在MySQL5.6下无效
SELECT WEEKOFYEAR('2017-05-15 10:37:14.123456');-- 同week()
SELECT DAYOFYEAR('2017-05-15 10:37:14.123456');-- 135 (日期在年度中第几天)
SELECT DAYOFMONTH('2017-05-15 10:37:14.123456');-- 5 (日期在月度中第几天)
SELECT DAYOFWEEK('2017-05-15 10:37:14.123456');-- 2 (日期在周中第几天；周日为第一天)
SELECT WEEKDAY('2017-05-15 10:37:14.123456');-- 0
SELECT WEEKDAY('2017-05-21 10:37:14.123456');-- 6(与dayofweek()都表示日期在周的第几天，只是参考标准不同，weekday()周一为第0天，周日为第6天)
SELECT YEARWEEK('2017-05-15 10:37:14.123456');-- 201720(年和周)
  
SELECT EXTRACT(YEAR FROM '2017-05-15 10:37:14.123456');
SELECT EXTRACT(MONTH FROM '2017-05-15 10:37:14.123456');
SELECT EXTRACT(DAY FROM '2017-05-15 10:37:14.123456');
SELECT EXTRACT(HOUR FROM '2017-05-15 10:37:14.123456');
SELECT EXTRACT(MINUTE FROM '2017-05-15 10:37:14.123456');
SELECT EXTRACT(SECOND FROM '2017-05-15 10:37:14.123456');
SELECT EXTRACT(MICROSECOND FROM '2017-05-15 10:37:14.123456');
SELECT EXTRACT(QUARTER FROM '2017-05-15 10:37:14.123456');
SELECT EXTRACT(WEEK FROM '2017-05-15 10:37:14.123456');
SELECT EXTRACT(YEAR_MONTH FROM '2017-05-15 10:37:14.123456');
SELECT EXTRACT(DAY_HOUR FROM '2017-05-15 10:37:14.123456');
SELECT EXTRACT(DAY_MINUTE FROM '2017-05-15 10:37:14.123456');-- 151037(日时分)
SELECT EXTRACT(DAY_SECOND FROM '2017-05-15 10:37:14.123456');-- 15103714(日时分秒)
SELECT EXTRACT(DAY_MICROSECOND FROM '2017-05-15 10:37:14.123456');-- 15103714123456(日时分秒毫秒)
SELECT EXTRACT(HOUR_MINUTE FROM '2017-05-15 10:37:14.123456');-- 1037(时分)
SELECT EXTRACT(HOUR_SECOND FROM '2017-05-15 10:37:14.123456');-- 103714(时分秒)
SELECT EXTRACT(HOUR_MICROSECOND FROM '2017-05-15 10:37:14.123456');-- 103714123456(日时分秒毫秒)
SELECT EXTRACT(MINUTE_SECOND FROM '2017-05-15 10:37:14.123456');-- 3714(分秒)
SELECT EXTRACT(MINUTE_MICROSECOND FROM '2017-05-15 10:37:14.123456');-- 3714123456(分秒毫秒)
SELECT EXTRACT(SECOND_MICROSECOND FROM '2017-05-15 10:37:14.123456');-- 14123456(秒毫秒)
-- MySQL Extract() 函数除了没有date(),time() 的功能外，其他功能一应具全。
-- 并且还具有选取‘day_microsecond' 等功能。
-- 注意这里不是只选取 day 和 microsecond，而是从日期的 day 部分一直选取到 microsecond 部分。
  
  
SELECT DAYNAME('2017-05-15 10:37:14.123456');-- Monday(返回英文星期)
SELECT MONTHNAME('2017-05-15 10:37:14.123456');-- May(返回英文月份)
SELECT LAST_DAY('2016-02-01');-- 2016-02-29 (返回月份中最后一天)
SELECT LAST_DAY('2016-05-01');-- 2016-05-31
  
-- DATE_ADD(date,INTERVAL expr type) 从日期加上指定的时间间隔
-- type参数可参考：http://www.w3school.com.cn/sql/func_date_sub.asp
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 YEAR);-- 表示：2018-05-15 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 QUARTER);-- 表示：2017-08-15 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MONTH);-- 表示：2017-06-15 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 WEEK);-- 表示：2017-05-22 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 DAY);-- 表示：2017-05-16 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 HOUR);-- 表示：2017-05-15 11:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MINUTE);-- 表示：2017-05-15 10:38:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 SECOND);-- 表示：2017-05-15 10:37:15.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MICROSECOND);-- 表示：2017-05-15 10:37:14.123457
  
  
-- DATE_SUB(date,INTERVAL expr type) 从日期减去指定的时间间隔
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 YEAR);-- 表示：2016-05-15 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 QUARTER);-- 表示：2017-02-15 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MONTH);-- 表示：2017-04-15 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 WEEK);-- 表示：2017-05-08 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 DAY);-- 表示：2017-05-14 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 HOUR);-- 表示：2017-05-15 09:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MINUTE);-- 表示：2017-05-15 10:36:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 SECOND);-- 表示：2017-05-15 10:37:13.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MICROSECOND);-- 表示：2017-05-15 10:37:14.123455
  
-- 经特殊日期测试，DATE_SUB(date,INTERVAL expr type)可放心使用
SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);-- 前一天：2017-05-11
SELECT DATE_SUB(CURDATE(),INTERVAL -1 DAY);-- 后一天：2017-05-13
SELECT DATE_SUB(CURDATE(),INTERVAL 1 MONTH);-- 一个月前日期：2017-04-12
SELECT DATE_SUB(CURDATE(),INTERVAL -1 MONTH);-- 一个月后日期：2017-06-12
SELECT DATE_SUB(CURDATE(),INTERVAL 1 YEAR);-- 一年前日期：2016-05-12
SELECT DATE_SUB(CURDATE(),INTERVAL -1 YEAR);-- 一年后日期：20178-06-12
-- MySQL date_sub() 日期时间函数 和 date_add() 用法一致，并且可以用INTERNAL -1 xxx的形式互换使用；
-- 另外，MySQL 中还有两个函数 subdate(), subtime()，建议，用 date_sub() 来替代。
  
-- MySQL 另类日期函数：period_add(P,N), period_diff(P1,P2)
-- 函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”，第二个参数“N” 表示增加或减去 N month（月）。
-- MySQL period_add(P,N)：日期加/减去N月。
SELECT PERIOD_ADD(201705,2), PERIOD_ADD(201705,-2);-- 201707  20170503
-- period_diff(P1,P2)：日期 P1-P2，返回 N 个月。
SELECT PERIOD_DIFF(201706, 201703);--
-- datediff(date1,date2)：两个日期相减 date1 - date2，返回天数
SELECT DATEDIFF('2017-06-05','2017-05-29');-- 7
-- TIMEDIFF(time1,time2)：两个日期相减 time1 - time2，返回 TIME 差值
SELECT TIMEDIFF('2017-06-05 19:28:37', '2017-06-05 17:00:00');-- 02:28:37
  
  
-- MySQL日期转换函数
SELECT TIME_TO_SEC('01:00:05'); -- 3605
SELECT SEC_TO_TIME(3605);-- 01:00:05
  
-- MySQL （日期、天数）转换函数：to_days(date), from_days(days)
SELECT TO_DAYS('0000-00-00'); -- NULL
SELECT TO_DAYS('2017-06-05'); -- 736850
SELECT FROM_DAYS(0);           -- '0000-00-00'
SELECT FROM_DAYS(736850);      -- '2017-06-05'
  
-- MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)
  
SELECT STR_TO_DATE('06.05.2017 19:40:30', '%m.%d.%Y %H:%i:%s');-- 2017-06-05 19:40:30
SELECT STR_TO_DATE('06/05/2017', '%m/%d/%Y');                  -- 2017-06-05
SELECT STR_TO_DATE('2017/12/3','%Y/%m/%d')             -- 2017-12-03
SELECT STR_TO_DATE('20:09:30', '%h:%i:%s')             -- NULL(超过12时的小时用小写h，得到的结果为NULL)
  
-- 日期时间格式化
SELECT DATE_FORMAT('2017-05-12 17:03:51', '%Y年%m月%d日 %H时%i分%s秒');-- 2017年05月12日 17时03分51秒(具体需要什么格式的数据根据实际情况来;小写h为12小时制;)
SELECT TIME_FORMAT('2017-05-12 17:03:51', '%Y年%m月%d日 %H时%i分%s秒');-- 0000年00月00日 17时03分51秒(time_format()只能用于时间的格式化)
-- STR_TO_DATE()和DATE_FORMATE()为互逆操作
  
-- MySQL 获得国家地区时间格式函数：get_format()
-- MySQL get_format() 语法：get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'
-- MySQL get_format() 用法的全部示例：
SELECT GET_FORMAT(DATE,'usa');      -- '%m.%d.%Y'
SELECT GET_FORMAT(DATE,'jis');      -- '%Y-%m-%d'
SELECT GET_FORMAT(DATE,'iso');      -- '%Y-%m-%d'
SELECT GET_FORMAT(DATE,'eur');      -- '%d.%m.%Y'
SELECT GET_FORMAT(DATE,'internal');     -- '%Y%m%d'
SELECT GET_FORMAT(DATETIME,'usa');      -- '%Y-%m-%d %H.%i.%s'
SELECT GET_FORMAT(DATETIME,'jis');      -- '%Y-%m-%d %H:%i:%s'
SELECT GET_FORMAT(DATETIME,'iso');      -- '%Y-%m-%d %H:%i:%s'
SELECT GET_FORMAT(DATETIME,'eur');      -- '%Y-%m-%d %H.%i.%s'
SELECT GET_FORMAT(DATETIME,'internal'); -- '%Y%m%d%H%i%s'
SELECT GET_FORMAT(TIME,'usa');      -- '%h:%i:%s %p'
SELECT GET_FORMAT(TIME,'jis');      -- '%H:%i:%s'
SELECT GET_FORMAT(TIME,'iso');      -- '%H:%i:%s'
SELECT GET_FORMAT(TIME,'eur');      -- '%H.%i.%s'
SELECT GET_FORMAT(TIME,'internal');     -- '%H%i%s'
  
  
-- MySQL 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)
SELECT MAKEDATE(2017,31);   -- '2017-01-31'
SELECT MAKEDATE(2017,32);   -- '2017-02-01'
SELECT MAKETIME(19,52,35);  -- '19:52:35'
  
-- MySQL 时区（timezone）转换函数：convert_tz(dt,from_tz,to_tz)
SELECT CONVERT_TZ('2017-06-05 19:54:12', '+08:00', '+00:00'); -- 2017-06-05 11:54:12
  
  
-- MySQL （Unix 时间戳、日期）转换函数
-- unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp,format)
-- 将具体时间时间转为timestamp
SELECT UNIX_TIMESTAMP();-- 当前时间的时间戳：1494815779
SELECT UNIX_TIMESTAMP('2017-05-15');-- 指定日期的时间戳：1494777600
SELECT UNIX_TIMESTAMP('2017-05-15 10:37:14');-- 指定日期时间的时间戳：1494815834
  
-- 将时间戳转为具体时间
SELECT FROM_UNIXTIME(1494815834);-- 2017-05-15 10:37:14
SELECT FROM_UNIXTIME(1494815834, '%Y年%m月%d日 %h时%分:%s秒');-- 获取时间戳对应的格式化日期时间
  
-- MySQL 时间戳（timestamp）转换、增、减函数
SELECT TIMESTAMP('2017-05-15');-- 2017-05-15 00:00:00
SELECT TIMESTAMP('2017-05-15 08:12:25', '01:01:01');-- 2017-05-15 09:13:26
SELECT DATE_ADD('2017-05-15 08:12:25', INTERVAL 1 DAY);-- 2017-05-16 08:12:25
SELECT TIMESTAMPADD(DAY, 1, '2017-05-15 08:12:25');-- 2017-05-16 08:12:25; MySQL timestampadd() 函数类似于 date_add()。
  
SELECT TIMESTAMPDIFF(YEAR, '2017-06-01', '2016-05-15');-- -1
SELECT TIMESTAMPDIFF(MONTH, '2017-06-01', '2016-06-15');-- -11
SELECT TIMESTAMPDIFF(DAY, '2017-06-01', '2016-06-15');-- -351
SELECT TIMESTAMPDIFF(HOUR, '2017-06-01 08:12:25', '2016-06-15 00:00:00');-- -8432
SELECT TIMESTAMPDIFF(MINUTE, '2017-06-01 08:12:25', '2016-06-15 00:00:00');-- -505932
SELECT TIMESTAMPDIFF(SECOND, '2017-06-01 08:12:25', '2016-06-15 00:00:00');-- -30355945

　　

————————————————
版权声明：本文为CSDN博主「HaleyTiger」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/hu1010037197/article/details/115391335

　　

　　

　　

posted @   九五小庞  阅读(387)  评论(0)  编辑  收藏  举报
相关博文：
· Mariadb取24小时数据--九五小庞
· mysql截取字符串函数 --九五小庞
· Mysql时间日期函数
· MySQL常用日期时间函数
· MySQL之日期函数和时间函数总结
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
点击右上角即可分享
微信分享提示