MySQL时间日期处理函数
2019-03-26 本文已影响0人
竹鼠不要中暑
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)
