备注:测试数据库版本为MySQL 8.0
这个blog我们来聊聊常见的日期时间函数
Table of Contents
从Oracle转到Mysql，对于日期时间函数，真的有点不适应，这个blog，我按照Oracle的使用习惯，把最常用的日期时间函数进行整理
一.时间格式函数
DATE_FORMAT()
-- 按照年月日时分秒形式显示日期时间
SELECT DATE_FORMAT('2020-05-22 13:00:00', '%Y-%m-%d %H:%i:%s');
-- 显示年月日时分秒 12小时制
SELECT DATE_FORMAT('2020-05-22 13:00:00', '%Y-%m-%d %h:%i:%s %p');
-- 显示英文月名
SELECT DATE_FORMAT('2020-05-22 13:00:00', '%M');
-- 显示英文月名简称
SELECT DATE_FORMAT('2020-05-22 13:00:00', '%b');
-- 显示星期英文名
SELECT DATE_FORMAT('2020-05-22 13:00:00', '%W');
-- 用数值显示今天周几，0代表周日
SELECT DATE_FORMAT('2020-05-22 13:00:00', '%w');
-- 按照年月日时分秒形式显示日期时间
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%Y-%m-%d %H:%i:%s');
+---------------------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%Y-%m-%d %H:%i:%s') |
+---------------------------------------------------------+
| 2020-05-22 13:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 显示年月日时分秒 12小时制
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%Y-%m-%d %h:%i:%s %p');
+------------------------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%Y-%m-%d %h:%i:%s %p') |
+------------------------------------------------------------+
| 2020-05-22 01:00:00 PM |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 显示英文月名
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%M');
+------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%M') |
+------------------------------------------+
| May |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 显示英文月名简称
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%b');
+------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%b') |
+------------------------------------------+
| May |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 显示星期英文名
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%W');
+------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%W') |
+------------------------------------------+
| Friday |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 用数值显示今天周几，0代表周日
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%w');
+------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%w') |
+------------------------------------------+
| 5 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 用数值显示今天是本年的第几周，星期一是周的第一天
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%x-%v');
+---------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%x-%v') |
+---------------------------------------------+
| 2020-21 |
+---------------------------------------------+
1 row in set (0.00 sec)
二.时间截取函数
可进行截取:
MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
select YEAR(DT), -- 截取年
QUARTER(DT), -- 截取季度
MONTH(DT), -- 截取月份
DAY(DT), -- 截取日
HOUR(DT), -- 截取小时
MINUTE(DT), -- 截取分
SECOND(DT), -- 截取秒
MICROSECOND(DT) -- 截取微妙
select '2020-05-22 23:50:59.000001' dt
) TMP;
mysql> select YEAR(DT), -- 截取年
-> QUARTER(DT), -- 截取季度
-> MONTH(DT), -- 截取月份
-> DAY(DT), -- 截取日
-> HOUR(DT), -- 截取小时
-> MINUTE(DT), -- 截取分
-> SECOND(DT), -- 截取秒
-> MICROSECOND(DT) -- 截取微妙
-> from
-> select '2020-05-22 23:50:59.000001' dt
-> ) TMP;
+----------+-------------+-----------+---------+----------+------------+------------+-----------------+
| YEAR(DT) | QUARTER(DT) | MONTH(DT) | DAY(DT) | HOUR(DT) | MINUTE(DT) | SECOND(DT) | MICROSECOND(DT) |
+----------+-------------+-----------+---------+----------+------------+------------+-----------------+
| 2020 | 2 | 5 | 22 | 23 | 50 | 59 | 1 |
+----------+-------------+-----------+---------+----------+------------+------------+-----------------+
1 row in set (0.00 sec)
三.时期时间运算
1.日期时间增加间隔
select TIMESTAMPADD(YEAR,1,DT) Y, -- 增加1年
TIMESTAMPADD(QUARTER,1,DT) Q, -- 增加1季度
TIMESTAMPADD(MONTH,1,DT) M, -- 增加1个月
TIMESTAMPADD(WEEK,1,DT) W, -- 增加1周
TIMESTAMPADD(DAY,1,DT) D, -- 增加1天
TIMESTAMPADD(HOUR,1,DT) H, -- 增加1小时
TIMESTAMPADD(MINUTE,1,DT) MI, -- 增加1分钟
TIMESTAMPADD(SECOND,1,DT) S, -- 增加1秒
TIMESTAMPADD(MICROSECOND,1,DT) MS -- 增加1微妙
select '2020-05-22 23:50:59' dt
) TMP;
2.时间间隔
DATEDIFF() 返回两个日期间隔的天数
TIMEDIFF() 返回两个时间的差，结果为时间类型
TIMESTAMPDIFF() 求两个日期时间的间隔
具体用法相关:
DATEDIFF()
--计算2个时间间隔的天数
mysql> select datediff('2020-05-22','2020-05-01');
+-------------------------------------+
| datediff('2020-05-22','2020-05-01') |
+-------------------------------------+
| 21 |
+-------------------------------------+
1 row in set (0.00 sec)
--有时分秒的话，都截取了，只比较日期部分
mysql>
mysql> select datediff('2020-05-22','2020-05-01 23:59:59');
+----------------------------------------------+
| datediff('2020-05-22','2020-05-01 23:59:59') |
+----------------------------------------------+
| 21 |
+----------------------------------------------+
1 row in set (0.00 sec)
TIMEDIFF()
mysql> select timediff('2020-05-22 23:50:59.000001','2020-05-22 23:50:59.000003');
+---------------------------------------------------------------------+
| timediff('2020-05-22 23:50:59.000001','2020-05-22 23:50:59.000003') |
+---------------------------------------------------------------------+
| -00:00:00.000002 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
TIMESTAMPDIFF()
可进行计算间隔:
MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
-- 求2个时间间隔的月份
mysql> SELECT TIMESTAMPDIFF(MONTH,'2020-02-01','2020-05-01');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2020-02-01','2020-05-01') |
+------------------------------------------------+
| 3 |
+------------------------------------------------+
1 row in set (0.00 sec)
-- 默认向下取整，没有带小数
mysql> SELECT TIMESTAMPDIFF(MONTH,'2020-02-01','2020-05-02');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2020-02-01','2020-05-02') |
+------------------------------------------------+
| 3 |
+------------------------------------------------+
1 row in set (0.00 sec)
四.字符串转为日期时间类型
STR_TO_DATE() 将字符串转为日期时间格式
语法:STR_TO_DATE(str,format)
将字符串转为日期时间格式
-- 这样随意的格式就是字符串，可以转为日期时间类型
mysql> select STR_TO_DATE('20200522 23:50:59','%Y%m%d %H:%i:%s');
+----------------------------------------------------+
| STR_TO_DATE('20200522 23:50:59','%Y%m%d %H:%i:%s') |
+----------------------------------------------------+
| 2020-05-22 23:50:59 |
+----------------------------------------------------+
1 row in set (0.00 sec)
-- 这样标准的格式被mysql默认为时间类型了，不能再转了
mysql> select STR_TO_DATE('2020-05-22 23:50:59','%Y%m%d %H:%i:%s');
+------------------------------------------------------+
| STR_TO_DATE('2020-05-22 23:50:59','%Y%m%d %H:%i:%s') |
+------------------------------------------------------+
| NULL |
+------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
LAST_DAY 返回当月最后一天
MAKEDATE() 根据年份及当年的天数，返回日期
MAKETIME() 根据时分秒，返回时间
1.LAST_DAY()
返回当月的最后一天，如果日期错误，返回null
mysql> SELECT LAST_DAY('2020-05-22');
+------------------------+
| LAST_DAY('2020-05-22') |
+------------------------+
| 2020-05-31 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT LAST_DAY('2020-05-32');
+------------------------+
| LAST_DAY('2020-05-32') |
+------------------------+
| NULL |
+------------------------+
1 row in set, 1 warning (0.00 sec)
2.MAKEDATE
根据年份和当年的第几天，返回日期
语法:MAKEDATE(year,dayofyear)
mysql> select makedate(2020,100);
+--------------------+
| makedate(2020,100) |
+--------------------+
| 2020-04-09 |
+--------------------+
1 row in set (0.00 sec)
mysql> select makedate(2020,0);
+------------------+
| makedate(2020,0) |
+------------------+
| NULL |
+------------------+
1 row in set (0.00 sec)
mysql> select makedate(2020,null);
+---------------------+
| makedate(2020,null) |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.00 sec)
3.MAKETIME
根据时分秒构造时间
语法:MAKETIME(hour,minute,second)
mysql> SELECT MAKETIME(12,15,30);
+--------------------+
| MAKETIME(12,15,30) |
+--------------------+
| 12:15:30 |
+--------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> SELECT MAKETIME(12,15,0);
+-------------------+
| MAKETIME(12,15,0) |
+-------------------+
| 12:15:00 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(12,15,null);
+----------------------+
| MAKETIME(12,15,null) |
+----------------------+
| NULL |