Soothingday
0%

MySQL常见的与日期相关的函数

发表于 更新于 分类于 ，

在MySQL中，需要用到日期相关的函数的场景还是非常多的，不论是时间戳和日期的转换，还是说不同日期间的数据比较，因此有必要记录一些常见的日期函数，巩固自己在这方面的知识点。

在开始本篇文章之前，先说一下MySQL常见的三种时间类型, date, datetime和timestamp三者之间的区别。

  1. date表示的是YYYY-MM-DD的形式，只能具体到日，而datetime和timestamp均可以表示到时分秒
  2. timestamp可以表示的时间范围为1970-01-01 00:00:01到2038-01-19 03:14:07，而datetime的话可以表示的时间会比较大，从 1000-01-01 00:00:00 到 9999-12-31 23:59:59
  3. timestamp表示的时间会根据当前数据库所在时区进行转换（存储时按照UTC时区存储，需要用的时候再转成数据库所在时区），而datetime的话时间是固定住的，不会因为时区变化而显示不同的时间
  4. timestamp占用4个字节存储，datetime占用8个字节存储，date占用3个字节存储

1. 获取当前时间

1.1 CURDATE() / CURRENT_DATE() / CURRENT_DATE

获取当前系统所在日期 

1
2
3
4
select CURDATE()

CURDATE()
2021-02-16

1.2 CURTIME() / CURRENT_TIME() / CURRENT_TIME

获取当前系统时间（时分秒） 

1
2
3
4
select CURTIME()

CURTIME()
14:22:16

1.3 NOW() / CURRENT_TIMESTAMP() / CURRENT_TIMESTAMP

获取当前系统时间(YYYY-MM-DD hh:mm:ss) 

1
2
3
4
select NOW(), CURRENT_TIMESTAMP

NOW()                 CURRENT_TIMESTAMP
2021-02-16 14:26:47   2021-02-16 14:26:47

1.4 SYSDATE()

获取当前系统时间(YYYY-MM-DD hh:mm:ss) 

1
2
3
4
select SYSDATE()

SYSDATE()           
2021-02-16 14:27:57

SYSDATE()和NOW()区别： NOW()记录的是对应语句刚开始执行的时间，而SYSDATE()记录的是执行到SYSDATE()的时候的确切时间

MySQL官方文档举了一个例子来说明NOW()和SYSDATE()的区别 

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

2. 日期加减

2.1 ADDDATE

该函数有两种用法，ADDDATE(date,INTERVAL expr unit)和ADDDATE(expr,days)
ADDDATE第一种用法等同于DATE_ADD(date,INTERVAL expr unit)函数
INTERVAL expr unit在这里说明是要在原日期的基础上加多少时间，例如INTERVAL 6 hour代表加上6小时，而INTERVAL -2 day代表减去2天。（如果是日期相减的SUBDATE函数，interval意义完全相反）
举个栗子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select date_add("2021-02-16 14:00:00", interval 5 hour), 
date_add("2021-02-16 14:00:00", interval 1 day),
date_add("2021-02-16 14:00:00", interval -2 day),
adddate("2021-02-16 14:00:00", INTERVAL '1 1:1:1' DAY_SECOND),
adddate("2021-02-16 14:00:00",3) ,
adddate("2021-02-16 14:00:00",-4)

每个语句分别输出的时间如下
2021-02-16 19:00:00	 
2021-02-17 14:00:00	 
2021-02-14 14:00:00	 
2021-02-17 15:01:01	 
2021-02-19 14:00:00	 
2021-02-12 14:00:00

2.2 ADDTIME

该函数用法如下，

ADDTIME(expr1,expr2)

其中expr1为日期/时间格式， expr2为时间格式
这里使用MySQL文档上的栗子 

1
2
3
4
mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
        -> '2008-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
        -> '03:00:01.999997'

2.3 SUBDATE

该函数有两种用法，SUBDATE(date,INTERVAL expr unit)和SUBDATE(expr,days)
SUBDATE第一种用法等同于DATE_SUB(date,INTERVAL expr unit)函数 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select date_sub("2021-02-16 14:00:00", interval 5 hour), 
date_sub("2021-02-16 14:00:00", interval 1 day),
date_sub("2021-02-16 14:00:00", interval -2 day),
subdate("2021-02-16 14:00:00", INTERVAL '1 1:1:1' DAY_SECOND),
subdate("2021-02-16 14:00:00",3) ,
subdate("2021-02-16 14:00:00",-4)

每个语句分别输出的时间如下
2021-02-16 09:00:00	 
2021-02-15 14:00:00	 
2021-02-18 14:00:00	 
2021-02-15 12:58:59	  
2021-02-13 14:00:00	  
2021-02-20 14:00:04

2.4 SUBTIME

该函数用法如下，

SUBTIME(expr1,expr2)

用法与ADDTIME类似，只是一个做加法，一个做减法

2.5 DATEDIFF

该函数用法如下，主要计算两个日期之间差几天 （expr1 - expr2)

DATEDIFF(expr1,expr2)
expr1, expr2需要为日期或者日期+时间的格式，计算的时候只对日期部分进行相减，时分秒不考虑 

1
2
3
4
5
6
7
8
select datediff("2021-02-16 14:00:00", "2021-02-15 12:00:00"),
datediff("2021-02-16 14:00:00", "2021-02-15 21:00:00"),  
datediff("2021-02-10", "2021-02-15 15:32:46")

每个语句分别输出的时间如下
1
1
-5

DATEDIFF和DATE_SUB区别：

  1. DATEDIFF没有参数为interval的形式
  2. 当DATE_SUB为不带interval类型参数的形式时，它们在参数上的区别为，DATEDIFF的第二个参数为日期，而DATE_SUB的参数是纯数字，即需要加或者减几天
  3. DATEDIFF只对日期进行计算，并返回一个整数。而DATESUB返回的是一个日期，且计算时如果有带时分秒，则会返回有时分秒的格式

2.6 TIMEDIFF

该函数用法如下，类似于datediff，但是计算的是两个时间的差值（可以精确到毫秒）

TIMEDIFF(expr1,expr2)
MySQL官方文档栗子: 

1
2
3
4
5
6
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
    ->                 '2000:01:01 00:00:00.000001');
        -> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',
    ->                 '2008-12-30 01:01:01.000002');
        -> '46:58:57.999999'

3. 改变日期的显示格式 DATE_FORMAT

DATE_FORMAT函数的使用形式如下：

DATE_FORMAT(date,format)

第一个参数为要转换格式的日期（字符串/数字/MySQL日期类型等都可
第二个参数为需要显示的日期格式（用法有些像C或者Python的print函数的格式化输出）

对应的格式内容很多，可参照官方文档相关内容。

直接上MySQL官方文档例子： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

DATE_FORMAT的反函数是STR_TO_DATE, 用法为STR_TO_DATE(str, format)，其中这里的format为第一个参数str对应的日期格式，如果str有包含日期及时间，则返回DATETIME类型；如果只有日期或时间，则返回DATE或TIME类型。如果都不匹配，则报错。

官方文档栗子如下： 

1
2
3
4
mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
        -> '2013-05-01'

4. UNIX_TIMESTAMP和DATETIME之间的转换

unix_timestamp记录的是从格林威治时间1970-01-01 00:00:00 到现在的总秒数，在很多的数据库中一般以long格式存储。在实际的生产生活中，经常会用到unix时间戳和正常时间的转换。它们之间互相转换的函数如下，

4.1 UNIX时间戳转日期 FROM_UNIXTIME()

该函数用法如下，

FROM_UNIXTIME(unix_timestamp[,format])

第一个参数为要转化的UNIX时间戳，第二个可选参数为显示的日期格式（默认为YYYY-MM-DD hh:mm:ss的格式）

以下是MySQL官方文档的例子

1
2
3
4
5
6
7
mysql> SELECT FROM_UNIXTIME(1447430881);
        -> '2015-11-13 10:08:01'
mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
        -> 20151113100801
mysql> SELECT FROM_UNIXTIME(1447430881,
    ->                      '%Y %D %M %h:%i:%s %x');
        -> '2015 13th November 10:08:01 2015'

4.2 日期转时间戳 UNIX_TIMESTAMP()

该函数用法如下

UNIX_TIMESTAMP([date])

如果没有参数，则返回当前时间的时间戳。
如果带有日期参数，则返回对应日期时间的时间戳。
date参数可以是MySQL的date、datetime以及timestamp格式，也可以是日期形式的字符串，或者像YYYYMMDDhhmmss形式的数字。
由于UNIX_TIMESTAMP最多到2038-01-19，所以如果超出界限，则函数返回0 

1
2
3
4
5
6
7
8
select unix_timestamp(),
unix_timestamp('2021-02-15 00:00:01'),
unix_timestamp(20210215)

对应的时间戳为：
1613460234	
1613318401	
1613318400

5. 时区转换

使用的是CONVERT_TZ函数，用法如下：

CONVERT_TZ(dt,from_tz,to_tz)

官网的例子如下： 

1
2
3
4
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
        -> '2004-01-01 13:00:00'
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'