MySQL时间日期处理函数

NOW()

返回当前的日期和时间

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-03-25 23:09:39 |
+---------------------+
1 row in set (0.04 sec)

DATE(), YEAR(), MONTH(), DAY(), TIME() ...

使用这些时间、日期的关键词，会返回一个日期的指定部分

mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2019-03-26  |
+-------------+
1 row in set (0.03 sec)

mysql> SELECT DAY(NOW());
+------------+
| DAY(NOW()) |
+------------+
|         26 |
+------------+
1 row in set (0.04 sec)

mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2019 |
+-------------+
1 row in set (0.03 sec)

mysql> SELECT MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|            3 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT TIME(NOW());
+-------------+
| TIME(NOW()) |
+-------------+
| 21:04:03    |
+-------------+
1 row in set (0.04 sec)

mysql> SELECT SECOND(NOW());
+---------------+
| SECOND(NOW()) |
+---------------+
|            12 |
+---------------+
1 row in set (0.00 sec)

CURDATE()

返回当前的日期

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-03-25 |
+------------+
1 row in set (0.03 sec)

CURTIME()

返回当前的时间

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 23:10:47  |
+-----------+
1 row in set (0.00 sec)

EXTRACT()

按要求返回日期/时间单独部分

mysql> SELECT EXTRACT(YEAR FROM NOW());
+--------------------------+
| EXTRACT(YEAR FROM NOW()) |
+--------------------------+
|                     2019 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(YEAR_MONTH FROM NOW());
+--------------------------------+
| EXTRACT(YEAR_MONTH FROM NOW()) |
+--------------------------------+
|                         201903 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(DAY FROM NOW());
+-------------------------+
| EXTRACT(DAY FROM NOW()) |
+-------------------------+
|                      25 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(MINUTE FROM NOW());
+----------------------------+
| EXTRACT(MINUTE FROM NOW()) |
+----------------------------+
|                         22 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(HOUR_MINUTE FROM NOW());
+---------------------------------+
| EXTRACT(HOUR_MINUTE FROM NOW()) |
+---------------------------------+
|                            2322 |
+---------------------------------+

DATE_ADD , DATE_SUB

DATE_ADD给日期添加指定的时间间隔;
DATE_SUB从日期减去指定的时间间隔;

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-03-25 23:37:17 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(), INTERVAL 28 DAY);
+----------------------------------+
| DATE_ADD(NOW(), INTERVAL 28 DAY) |
+----------------------------------+
| 2019-04-22 23:37:20              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR);
+----------------------------------+
| DATE_ADD(NOW(), INTERVAL 1 HOUR) |
+----------------------------------+
| 2019-03-26 00:37:22              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(), INTERVAL '12:18' MINUTE_SECOND);
+-------------------------------------------------+
| DATE_ADD(NOW(), INTERVAL '12:18' MINUTE_SECOND) |
+-------------------------------------------------+
| 2019-03-25 23:49:59                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB(NOW(), INTERVAL 10 YEAR);
+-----------------------------------+
| DATE_SUB(NOW(), INTERVAL 10 YEAR) |
+-----------------------------------+
| 2009-03-25 23:39:29               |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB(NOW(), INTERVAL '8-2' YEAR_MONTH);
+--------------------------------------------+
| DATE_SUB(NOW(), INTERVAL '8-2' YEAR_MONTH) |
+--------------------------------------------+
| 2011-01-25 23:40:02                        |
+--------------------------------------------+
1 row in set (0.00 sec)

ADDDATE(), ADDTIME(), SUBDATE(), SUBTIME()

给日期/时间，加上/减去指定的日期/时间间隔。因为函数已声明加减的间隔为时间或日期，因此不必像DATE_ADD和DATE_SUB方法那样再在参数列表中声名INTERVAL 1 DAY一类。

mysql> SELECT ADDDATE(NOW(),31);
+---------------------+
| ADDDATE(NOW(),31)   |
+---------------------+
| 2019-04-26 21:39:02 |
+---------------------+
1 row in set (0.03 sec)

mysql> SELECT SUBDATE(NOW(),31);
+---------------------+
| SUBDATE(NOW(),31)   |
+---------------------+
| 2019-02-23 21:39:08 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDTIME(NOW(),'1:0:0');
+------------------------+
| ADDTIME(NOW(),'1:0:0') |
+------------------------+
| 2019-03-26 22:41:10    |
+------------------------+
1 row in set (0.04 sec)

mysql> SELECT SUBTIME(NOW(),'1:0:0');
+------------------------+
| SUBTIME(NOW(),'1:0:0') |
+------------------------+
| 2019-03-26 20:41:20    |
+------------------------+
1 row in set (0.00 sec)

DATEDIFF

返回两个日期之间的天数

mysql> SELECT DATEDIFF(NOW(),'2008-8-8');
+----------------------------+
| DATEDIFF(NOW(),'2008-8-8') |
+----------------------------+
|                       3881 |
+----------------------------+
1 row in set (0.05 sec)

mysql> SELECT DATEDIFF('2008-8-8',NOW());
+----------------------------+
| DATEDIFF('2008-8-8',NOW()) |
+----------------------------+
|                      -3881 |
+----------------------------+
1 row in set (0.00 sec)

DATE_FORMAT

用不同的格式显示日期/时间
下表为一些格式说明符：

说明符	描述
%a	缩写星期名(Sun..Sat)
%b	缩写月名(Jan..Dec)
%c	数字月份(0..12)
%D	带有英文后缀的月中的天(0th, 1st, 2nd,...)
%d	月中的天，数值(00-31)
%e	月中的天，数值(0-31)
%f	微秒(000000..999999)
%H	小时 (00..23)
%h	小时 (01..12)
%I	小时 (01..12)
%i	分钟，数值(00..59)
%j	年中的天 (001..366)
%k	小时 (0..23)
%l	小时 (1..12)
%M	月名(January..December)
%m	月，数值(00..12)
%p	AM 或 PM
%r	时间，12小时（hh:mm:ss AM 或 PM）
%S	秒(00..59)
%s	秒(00..59)
%T	时间, 24小时制 (hh:mm:ss)
%U	周 (00..53) , 星期日是一周的第一天
%u	周 (00..53), 星期一是一周的第一天
%V	周 (01..53) 星期日是一周的第一天，与 %X 使用
%v	周 (01..53) 星期一是一周的第一天，与 %x 使用
%W	星期名 (Sunday..Saturday)
%w	周中的天 (0=星期日, 6=星期六)
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-03-26 21:30:50 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%W %M %Y');
+-------------------------------+
| DATE_FORMAT(NOW(),'%W %M %Y') |
+-------------------------------+
| Tuesday March 2019            |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%H:%i:%s');
+-------------------------------+
| DATE_FORMAT(NOW(),'%H:%i:%s') |
+-------------------------------+
| 21:31:23                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');
+----------------------------------------+
| DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') |
+----------------------------------------+
| Mar 26 2019 09:32 PM                   |
+----------------------------------------+
1 row in set (0.00 sec)