MySQL日期时间函数（转换、计算）整理总结

本篇介绍MySQL下和时间、日期相关的函数

以下SQL命令，大小写都是可以的

MySQL中的时间单位

MySQL时间单位说明

year	年
quarter	季度
month	月
week	周
day	天
hour	小时
hour	分钟
second	秒
microsecond	微秒

获得当前时间

-- 获得当前的时间+日期
SELECT NOW(); -- 得到 2021-10-06 09:02:28

-- 获得当前的时间+日期，也可以使用sysdate()函数
SELECT sysdate(); -- 得到 2021-10-06 09:02:28

-- sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。例子如下，我们在now()后延迟了3秒钟
select now(), sleep(3), now(), sysdate();

mysql> select now(), sleep(3), now(),sysdate();
+---------------------+----------+---------------------+---------------------+
| now() | sleep(3) | now() | sysdate() |
+---------------------+----------+---------------------+---------------------+
| 2021-10-06 09:07:40 | 0 | 2021-10-06 09:07:40 | 2021-10-06 09:07:43 |
+---------------------+----------+---------------------+---------------------+
1 row in set (3.00 sec)

-- 可以看到第二个now获得的时间和第一个now是一样的，但是sysdate获得的时间比now多了3秒

-- 得当前时间戳
SELECT CURRENT_TIMESTAMP()

日期转换函数

Date/Time to Str（日期/时间转换为字符串）函数：date_format(date,format), time_format(time,format)

mysql> select date_format('2021-10-06 09:23:01', '%Y%m%d%H%i%s');
+----------------------------------------------------+
| date_format('2021-10-06 09:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20211006092301                                     |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(), '%Y-%m-%d %H:%i:%s');
+-----------------------------------------+
| date_format(now(), '%Y-%m-%d %H:%i:%s') |
+-----------------------------------------+
| 2021-10-06 09:12:37                     |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select time_format(now(), '%H:%i:%s');
+--------------------------------+
| time_format(now(), '%H:%i:%s') |
+--------------------------------+
| 09:13:00                       |
+--------------------------------+
1 row in set (0.00 sec)

Str to Date （字符串转换为日期）函数：str_to_date(str, format)

mysql> select str_to_date('2021-10-01', '%Y-%m-%d');
+---------------------------------------+
| str_to_date('2021-10-01', '%Y-%m-%d') |
+---------------------------------------+
| 2021-10-01                            |
+---------------------------------------+
1 row in set (0.01 sec)

mysql> select str_to_date('2021-10-01 00:00:00', '%Y-%m-%d %H:%i:%s');
+---------------------------------------------------------+
| str_to_date('2021-10-01 00:00:00', '%Y-%m-%d %H:%i:%s') |
+---------------------------------------------------------+
| 2021-10-01 00:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

（日期、天数）转换函数：to_days(date), from_days(days)

计算从0000年1月1日以来的天数（注意不是1970年）

mysql> select to_days('0000-1-1');
+---------------------+
| to_days('0000-1-1') |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

mysql> select to_days('2021-10-06');
+-----------------------+
| to_days('2021-10-06') |
+-----------------------+
|                738434 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select from_days(738434); -- 2021-10-06

（时间、秒）转换函数：time_to_sec(time), sec_to_time(seconds)

计算一天内时间的秒数

select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'

拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'
select maketime(12,15,30); -- '12:15:30'

日期、时间计算函数介绍

在日期上增加一个时间间隔

select date_add(now(), interval 1 day); -- 增加1天
select date_add(now(), interval 1 hour); -- 增加1小时

-- interval 后面，可以跟着的其他时间单位，（详见最上面时间单位介绍）：
-- day 天
-- hour 小时
-- minute 分钟
-- second 秒
-- microsecond 微秒
-- week 周
-- month 月
-- quarter 季度
-- year 年

-- interval后面还可以跟着负数，表示减少，例如：
select date_add(now(), interval -1 day); -- 减少1天

日期、时间相减/相差函数：datediff(date1,date2), timediff(time1,time2)

-- 返回相差的天数
select datediff('2021-10-01', '2021-10-06'); -- 相差-5天
select datediff('2021-10-06', '2021-10-01'); -- 相差5天

-- 两个日期相减 time1 - time2，返回 time 差值
select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08

时间戳（timestamp）转换、增、减、相差函数

timestamp(date) -- date to timestamp 日期转成timestamp
timestamp(dt, time) -- dt + time
timestampadd(unit, interval, datetime_expr) --
timestampdiff(unit, datetime_expr1, datetime_expr2) --比较两个时间戳相差多少个单位，例如相差多小小时，多少天
-- unit 可以是year 年，month 月，day天等时间单位

示例：

select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01

select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00

MySQL timestampadd() 函数类似于 date_add()

-- 这里的时间单位详见最上面的时间单位介绍
select timestampdiff(year,'2002-05-01','2001-01-01'); -- 比较两个日期相差多少年，这里结果是 -1 年
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- 比较两个日期相差多少天 -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12

select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7

时区转换函数

convert_tz(dt,from_tz,to_tz)

select convert_tz('2021-08-08 12:00:00', '+08:00', '+00:00'); -- 2021-08-08 04:00:00