MySQL-常用函数(时间篇)

常用函数

• adddate(date, interval expr unit), adddate(date, days): 时间相加后返回
  • adddate(date, interval expr unit): date日期，expr间隔，unit属性，请看下面unit属性表
  • adddate(date, days): date日期，days天数

mysql> select curdate(), adddate(curdate(), interval 2 day), adddate(curdate(), 2);
+------------+------------------------------------+-----------------------+
| curdate()  | adddate(curdate(), interval 2 day) | adddate(curdate(), 2) |
+------------+------------------------------------+-----------------------+
| 2018-12-12 | 2018-12-14                         | 2018-12-14            |
+------------+------------------------------------+-----------------------+
1 row in set (0.00 sec)

• addtime(expr1, expr2): 将expr1与expr2相加得到时间后返回。使用如下：

mysql> select now(), addtime(now(), '1 1:1'), addtime(now(), '1 1:1:1'), addtime(now(), '1 1:1:1.1');
+---------------------+-------------------------+---------------------------+-----------------------------+
| now()               | addtime(now(), '1 1:1') | addtime(now(), '1 1:1:1') | addtime(now(), '1 1:1:1.1') |
+---------------------+-------------------------+---------------------------+-----------------------------+
| 2018-12-13 10:14:17 | 2018-12-14 11:15:17     | 2018-12-14 11:15:18       | 2018-12-14 11:15:18.1       |
+---------------------+-------------------------+---------------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> select now(), addtime(now(), '1'), addtime(now(), '1.1'), addtime(now(), '1:1'), addtime(now(), '1:1:1.1');
+---------------------+---------------------+-----------------------+-----------------------+---------------------------+
| now()               | addtime(now(), '1') | addtime(now(), '1.1') | addtime(now(), '1:1') | addtime(now(), '1:1:1.1') |
+---------------------+---------------------+-----------------------+-----------------------+---------------------------+
| 2018-12-13 10:14:39 | 2018-12-13 10:14:40 | 2018-12-13 10:14:40.1 | 2018-12-13 11:15:39   | 2018-12-13 11:15:40.1     |
+---------------------+---------------------+-----------------------+-----------------------+---------------------------+
1 row in set (0.00 sec)

• curdate(): 获取当前日期

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2018-11-11 |
+------------+
1 row in set (0.00 sec)

• current_date(): 获取当前日期，效果与curdate()一样
• curtime(): 获取当前时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 10:24:00  |
+-----------+
1 row in set (0.00 sec)

• current_time(): 获取当前时间，效果与curtime()一样
• date(expr): 获取指定日期时间expr的日期

mysql> select now(), date(now());
+---------------------+-------------+
| now()               | date(now()) |
+---------------------+-------------+
| 2018-12-13 10:26:37 | 2018-12-13  |
+---------------------+-------------+
1 row in set (0.00 sec)

• datediff(expr1, expr2): 返回expr1 - expr2之间的天数，expr1与expr2可以是日期，也可以是日期时间。值得注意的是，只会计算expr1与expr2之间的日期部分：

mysql> select datediff('2018-12-12', '2018-12-11'), datediff('2018-12-12', '2018-12-13'), datediff('2018-12-12', '2018-12-11 23:00:00');
+--------------------------------------+--------------------------------------+-----------------------------------------------+
| datediff('2018-12-12', '2018-12-11') | datediff('2018-12-12', '2018-12-13') | datediff('2018-12-12', '2018-12-11 23:00:00') |
+--------------------------------------+--------------------------------------+-----------------------------------------------+
|                                    1 |                                   -1 |                                             1 |
+--------------------------------------+--------------------------------------+-----------------------------------------------+
1 row in set (0.00 sec)

• date_add(date, interval expr unit): 日期算术，date加上时间间隔后返回。date可以是日期，也可以是日期时间。expr间隔，unit属性，请看下面unit属性表

mysql> select date_add('2018-12-12 23:00:00', interval 1 day), date_add('2018-12-12 23:00:00', interval -1 day);
+-------------------------------------------------+--------------------------------------------------+
| date_add('2018-12-12 23:00:00', interval 1 day) | date_add('2018-12-12 23:00:00', interval -1 day) |
+-------------------------------------------------+--------------------------------------------------+
| 2018-12-13 23:00:00                             | 2018-12-11 23:00:00                              |
+-------------------------------------------------+--------------------------------------------------+
1 row in set (0.01 sec)

• date_sub(date, interval expr unit): 日期算术，date减去时间间隔后返回。date可以是日期，也可以是日期时间。expr间隔，unit属性，请看下面unit属性表

mysql> select date_sub('2018-12-12 23:00:00', interval 1 day), date_sub('2018-12-12 23:00:00', interval -1 day);
+-------------------------------------------------+--------------------------------------------------+
| date_sub('2018-12-12 23:00:00', interval 1 day) | date_sub('2018-12-12 23:00:00', interval -1 day) |
+-------------------------------------------------+--------------------------------------------------+
| 2018-12-11 23:00:00                             | 2018-12-13 23:00:00                              |
+-------------------------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

• date_format(date, format): date根据format格式化值

mysql> select @date:='2018-12-12';
mysql> select date_format(@date, '%a'), date_format(@date, '%b'), date_format(@date, '%c'), date_format(@date, '%D'), date_format(@date, '%d'), date_format(@date, '%e'), date_format(@date, '%f'), date_format(@date, '%H'), date_format(@date, '%h'), date_format(@date, '%I'), date_format(@date, '%i'), date_format(@date, '%j'), date_format(@date, '%k'), date_format(@date, '%l'), date_format(@date, '%M'), date_format(@date, '%m'), date_format(@date, '%p'), date_format(@date, '%r');
+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+-------------------------