常用函数
- 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)
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');
+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+-------------------------