ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > MySQL 日期时间函数大全

MySQL 日期时间函数大全

原创 MySQL 作者：神谕丶 时间：2015-09-25 11:14:28 0 删除 编辑
以下内容基于MySQL 5.6及更高，大部分函数5.5也基本适用，更低版本请参考对应版本手册，其内容整理自官方。

mysql常用函数被分为五类，分别为：

①Date and time：日期和时间；

②String：字符串函数；
http://blog.itpub.net/29773961/viewspace-1813545/

③Numeric：数字函数；
http://blog.itpub.net/29773961/viewspace-1813556/

④Control Flow：基于一个表达式的结果集选择不同的值（控制流函数）；
http://blog.itpub.net/29773961/viewspace-1813557/

⑤Aggregate：基于一列的多个值返回单一值（聚合函数）；
http://blog.itpub.net/29773961/viewspace-1813589/

【Date and time】：

（补充说明）DATE/TIME 格式：



NOW()、CURRENT_TIMESTAMP()与CURRENT_TIMESTAMP、LOCALTIME()与LOCALTIME、LOCALTIMESTAMP()与LOCALTIMESTAMP：（均为同义词）
返回当前的日期和时间，基于Satement（DATETIME格式）
  1. mysql> SELECT NOW(),CURRENT_TIMESTAMP();
  2. +---------------------+---------------------+
  3. | NOW()               | CURRENT_TIMESTAMP() |
  4. +---------------------+---------------------+
  5. | 2015-09-25 14:14:15 | 2015-09-25 14:14:15 |
  6. +---------------------+---------------------+
  7. 1 row in set (0.00 sec)
  1. mysql> SELECT NOW(),LOCALTIME(),LOCALTIME,LOCALTIMESTAMP(),LOCALTIMESTAMP;
  2. +---------------------+---------------------+---------------------+---------------------+---------------------+
  3. | NOW()               | LOCALTIME()         | LOCALTIME           | LOCALTIMESTAMP()    | LOCALTIMESTAMP      |
  4. +---------------------+---------------------+---------------------+---------------------+---------------------+
  5. | 2015-09-25 14:21:23 | 2015-09-25 14:21:23 | 2015-09-25 14:21:23 | 2015-09-25 14:21:23 | 2015-09-25 14:21:23 | 
  6. +---------------------+---------------------+---------------------+---------------------+---------------------+
  7. 1 row in set (0.00 sec)


SYSDATE()：返回当前的日期和时间，基于系统时间（DATETIME格式）
  1. mysql> mysql> SELECT NOW(),SYSDATE(),SLEEP(1),NOW(),SYSDATE();
  2. +---------------------+---------------------+----------+---------------------+---------------------+
  3. | NOW()               | SYSDATE()           | SLEEP(1) | NOW()               | SYSDATE()           |
  4. +---------------------+---------------------+----------+---------------------+---------------------+
  5. | 2015-09-28 10:49:00 | 2015-09-28 10:49:00 | 0        | 2015-09-28 10:49:00 | 2015-09-28 10:49:01 |
  6. +---------------------+---------------------+----------+---------------------+---------------------+
  7. 1 row in set (1.00 sec)


CURDATE()与CURRENT_DATE()与CURRENT_DATE：返回当前的日期，基于服务器主机（DATE格式）
  1. mysql> SELECT CURDATE(),CURRENT_DATE();
  2. +------------+----------------+
  3. | CURDATE()  | CURRENT_DATE() |
  4. +------------+----------------+
  5. | 2015-09-25 | 2015-09-25     |
  6. +------------+----------------+
  7. 1 row in set (0.00 sec)


CURTIME()与CURRENT_TIME()与CURRENT_TIME： 返回当前的时间，基于服务器主机（TIME格式）
  1. mysql> SELECT CURTIME(),CURRENT_TIME();
  2. +-----------+----------------+
  3. | CURTIME() | CURRENT_TIME() |
  4. +-----------+----------------+
  5. | 14:15:19  | 14:15:19       |
  6. +-----------+----------------+
  7. 1 row in set (0.00 sec)


YEAR()：返回日期的年份（YEAR格式）
MONTH()：返回日期的月份
DAY()与DAYOFMONTH()：返回日期的天数
DAYNAME()：返回星期几
  1. mysql> SELECT YEAR(NOW()),
  2.     -> MONTH(NOW()),
  3.     -> DAY(NOW()),
  4.     -> DAYOFMONTH(NOW()),
  5.     -> DAYNAME(NOW());
  6. +-------------+--------------+------------+-------------------+----------------+
  7. | YEAR(NOW()) | MONTH(NOW()) | DAY(NOW()) | DAYOFMONTH(NOW()) | DAYNAME(NOW()) |
  8. +-------------+--------------+------------+-------------------+----------------+
  9. | 2015        | 9            | 25         | 25                | Friday         |
  10. +-------------+--------------+------------+-------------------+----------------+
  11. 1 row in set (0.00 sec)


DAYOFWEEK()：返回日期对应的星期，1--周日 2--周一 3--周二 4--周三 5--周四 6--周五 7--周六
DAYOFYEAR()：返回日期为该年的第多少天
  1. mysql> \! cal
  2.    September 2015
  3. Su Mo Tu We Th Fr Sa
  4.        1 2 3 4 5
  5.  6 7 8 9 10 11 12
  6. 13 14 15 16 17 18 19
  7. 20 21 22 23 24 25 26
  8. 27 28 29 30

  10. mysql> SELECT DAYOFWEEK(NOW()),
  11.     -> DAYOFYEAR(NOW());
  12. +------------------+------------------+
  13. | DAYOFWEEK(NOW()) | DAYOFYEAR(NOW()) |
  14. +------------------+------------------+
  15. | 2                | 271              |
  16. +------------------+------------------+
  17. 1 row in set (0.00 sec)


HOUR()：提取时间的小时
MINUTE()：提取时间的分钟
SECOND()：提取时间的秒数
MICROSECOND()：提取时间的微秒
  1. mysql> SELECT HOUR('11:11:12.000123') h,
  2.     -> MINUTE('11:11:12.000123') m,
  3.     -> SECOND('11:11:12.000123') s,
  4.     -> MICROSECOND('11:11:12.000123') ms;
  5. +------+------+------+------+
  6. | h    | m    | s    | ms   |
  7. +------+------+------+------+
  8. | 11   | 11   | 12   | 123  |
  9. +------+------+------+------+
  10. 1 row in set (0.00 sec)


STR_TO_DATE()：将字符串转换成日期格式（以下三例均可用DATE_FORMAT()替换）
  1. mysql> SELECT STR_TO_DATE('2015-10-31','%Y-%m-%d');
  2. +--------------------------------------+
  3. | STR_TO_DATE('2015-10-31','%Y-%m-%d') |
  4. +--------------------------------------+
  5. | 2015-10-31                           |
  6. +--------------------------------------+
  7. 1 row in set (0.00 sec)

  9. mysql> SELECT STR_TO_DATE('2015-10-31 08:30:59','%Y-%m-%d %H:%i:%s');
  10. +--------------------------------------------------------+
  11. | STR_TO_DATE('2015-10-31 08:30:59','%Y-%m-%d %H:%i:%s') |
  12. +--------------------------------------------------------+
  13. | 2015-10-31 08:30:59                                    |
  14. +--------------------------------------------------------+
  15. 1 row in set (0.00 sec)
  1. mysql> SELECT STR_TO_DATE('20151031083059','%Y%m%d%H%i%s');    -- 格式化的格式要与字符串一致
  2. +----------------------------------------------+
  3. | STR_TO_DATE('20151031083059','%Y%m%d%H%i%s') |
  4. +----------------------------------------------+
  5. | 2015-10-31 08:30:59                          |
  6. +----------------------------------------------+
  7. 1 row in set (0.00 sec)


DATE_FORMAT()：将日期时间格式化
  1. mysql> SELECT DATE_FORMAT('2015-10-31 08:30:59','%H:%i:%s');    
  2. +-----------------------------------------------+
  3. | DATE_FORMAT('2015-10-31 08:30:59','%H:%i:%s') |
  4. +-----------------------------------------------+
  5. | 08:30:59                                      |
  6. +-----------------------------------------------+
  7. 1 row in set (0.00 sec)

  9. mysql> SELECT DATE_FORMAT(NOW(),'%y/%m/%d');    -- 也可以这样用
  10. +-------------------------------+
  11. | DATE_FORMAT(NOW(),'%y/%m/%d') |
  12. +-------------------------------+
  13. | 15/09/25                      |
  14. +-------------------------------+
  15. 1 row in set (0.00 sec)
一般情况下，多数场景可用DATE_FORMAT()代替STR_TO_DATE()。


ADDDATE()：将日期相加减
DATE_ADD()与DATE_SUB()和SUBDATE()：将日期相加减
  1. mysql> SELECT ADDDATE('2000-01-01',222);
  2. +---------------------------+
  3. | ADDDATE('2000-01-01',222) |
  4. +---------------------------+
  5. | 2000-08-10                |
  6. +---------------------------+
  7. 1 row in set (0.00 sec)

  8.
  9. mysql> SELECT ADDDATE('2000-01-01', INTERVAL 111 DAY);
  10. +-----------------------------------------+
  11. | ADDDATE('2000-01-01', INTERVAL 111 DAY) |
  12. +-----------------------------------------+
  13. | 2000-04-21                              |
  14. +-----------------------------------------+
  15. 1 row in set (0.00 sec)

  17. mysql> SELECT DATE_ADD('2000-01-01', INTERVAL 111 DAY);
  18. +------------------------------------------+
  19. | DATE_ADD('2000-01-01', INTERVAL 111 DAY) |
  20. +------------------------------------------+
  21. | 2000-04-21                               |
  22. +------------------------------------------+
  23. 1 row in set (0.00 sec)


ADDTIME()：将日期与时间相加减
  1. mysql> SELECT ADDTIME('2000-01-01 00:00:01.000000', '1 1:1:1.000001');
  2. +---------------------------------------------------------+
  3. | ADDTIME('2000-01-01 00:00:01.000000', '1 1:1:1.000001') |
  4. +---------------------------------------------------------+
  5. | 2000-01-02 01:01:02.000001                              |
  6. +---------------------------------------------------------+
  7. 1 row in set (0.00 sec)

  9. mysql> SELECT ADDTIME('11:11:11.999999', '1:0:0.000001');
  10. +--------------------------------------------+
  11. | ADDTIME('11:11:11.999999', '1:0:0.000001') |
  12. +--------------------------------------------+
  13. | 12:11:12                                   |
  14. +--------------------------------------------+
  15. 1 row in set (0.00 sec)


CONVERT_TZ()：转换时区
  1. mysql> SELECT CONVERT_TZ('2000-01-01 12:00:00', '+00:00', '+08:00');    -- 将+0转换为+8
  2. +-------------------------------------------------------+
  3. | CONVERT_TZ('2000-01-01 12:00:00', '+00:00', '+08:00') |
  4. +-------------------------------------------------------+
  5. | 2000-01-01 20:00:00                                   |
  6. +-------------------------------------------------------+
  7. 1 row in set (0.00 sec)


DATE()：将日期时间转换成日期
  1. mysql> SELECT DATE(NOW());
  2. +-------------+
  3. | DATE(NOW()) |
  4. +-------------+
  5. | 2015-09-28  |
  6. +-------------+
  7. 1 row in set (0.00 sec)


DATEDIFF()：计算两个日期的差值
  1. mysql> SELECT DATEDIFF(NOW(),'1993-06-12');
  2. +------------------------------+
  3. | DATEDIFF(NOW(),'1993-06-12') |
  4. +------------------------------+
  5. | 8143                         |
  6. +------------------------------+
  7. 1 row in set (0.00 sec)


额外参数：INTERVAL，用于日期或时间相加（相减），更加灵活。
  1. mysql> SELECT NOW(),
  2.     -> NOW() + INTERVAL 100 DAY,
  3.     -> NOW() - INTERVAL 299 MINUTE;
  4. +---------------------+--------------------------+-----------------------------+
  5. | NOW()               | NOW() + INTERVAL 100 DAY | NOW() - INTERVAL 299 MINUTE |
  6. +---------------------+--------------------------+-----------------------------+
  7. | 2015-09-25 14:39:59 | 2016-01-03 14:39:59      | 2015-09-25 09:40:59         |
  8. +---------------------+--------------------------+-----------------------------+
  9. 1 row in set (0.00 sec)


EXTRACT()：提取日期时间的部分内容
  1. mysql> SELECT EXTRACT(YEAR FROM '2000-01-02 01:02:03'),
  2.     -> EXTRACT(YEAR_MONTH FROM '2000-01-02 01:02:03'),
  3.     -> EXTRACT(DAY_MINUTE FROM '2000-01-02 01:02:03'),
  4.     -> EXTRACT(MICROSECOND FROM '2000-01-02 01:02:03.000123')\G
  5. *************************** 1. row ***************************
  6.               EXTRACT(YEAR FROM '2000-01-02 01:02:03'): 2000
  7.         EXTRACT(YEAR_MONTH FROM '2000-01-02 01:02:03'): 200001
  8.         EXTRACT(DAY_MINUTE FROM '2000-01-02 01:02:03'): 20102
  9. EXTRACT(MICROSECOND FROM '2000-01-02 01:02:03.000123'): 123
  10. 1 row in set (0.00 sec)


FROM_DAYS()：根据整型数字N（天）返回对应日期
  1. mysql> SELECT FROM_DAYS(366);
  2. +----------------+
  3. | FROM_DAYS(366) |
  4. +----------------+
  5. | 0001-01-01     |
  6. +----------------+
  7. 1 row in set (0.00 sec)

  9. mysql> SELECT FROM_DAYS(735555);
  10. +-------------------+
  11. | FROM_DAYS(735555) |
  12. +-------------------+
  13. | 2013-11-18        |
  14. +-------------------+
  15. 1 row in set (0.00 sec)


FROM_UNIXTIME()：从UNIX时间戳开始返回经过N秒后的时间
  1. mysql> SELECT FROM_UNIXTIME(0);
  2. +---------------------+
  3. | FROM_UNIXTIME(0)    |
  4. +---------------------+
  5. | 1970-01-01 08:00:00 |
  6. +---------------------+
  7. 1 row in set (0.00 sec)

  9. mysql> SELECT FROM_UNIXTIME(1);
  10. +---------------------+
  11. | FROM_UNIXTIME(1)    |
  12. +---------------------+
  13. | 1970-01-01 08:00:01 |
  14. +---------------------+
  15. 1 row in set (0.00 sec)

  17. mysql> SELECT FROM_UNIXTIME(3600);
  18. +---------------------+
  19. | FROM_UNIXTIME(3600) |
  20. +---------------------+
  21. | 1970-01-01 09:00:00 |
  22. +---------------------+
  23. 1 row in set (0.00 sec)


GET_FORMAT()：返回格式字符串
一般在使用DATE_FORMAT时和STR_TO_DATE()时配合。
  1. Function Call                        Result
  2. GET_FORMAT(DATE,'USA')             '%m.%d.%Y'
  3. GET_FORMAT(DATE,'JIS')             '%Y-%m-%d'
  4. GET_FORMAT(DATE,'ISO')             '%Y-%m-%d'
  5. GET_FORMAT(DATE,'EUR')             '%d.%m.%Y'
  6. GET_FORMAT(DATE,'INTERNAL')        '%Y%m%d'
  7. GET_FORMAT(DATETIME,'USA')         '%Y-%m-%d %H.%i.%s'
  8. GET_FORMAT(DATETIME,'JIS')         '%Y-%m-%d %H:%i:%s'
  9. GET_FORMAT(DATETIME,'ISO')         '%Y-%m-%d %H:%i:%s'
  10. GET_FORMAT(DATETIME,'EUR')         '%Y-%m-%d %H.%i.%s'
  11. GET_FORMAT(DATETIME,'INTERNAL')    '%Y%m%d%H%i%s'
  12. GET_FORMAT(TIME,'USA')             '%h:%i:%s %p'
  13. GET_FORMAT(TIME,'JIS')             '%H:%i:%s'
  14. GET_FORMAT(TIME,'ISO')             '%H:%i:%s'
  15. GET_FORMAT(TIME,'EUR')             '%H.%i.%s'
  16. GET_FORMAT(TIME,'INTERNAL')        '%H%i%s'
  1. mysql> SELECT GET_FORMAT(DATE,'USA') a,
  2.     -> STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA')) b,
  3.     -> GET_FORMAT(DATE,'EUR') c,
  4.     -> DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR')) d;
  5. +----------+------------+----------+------------+
  6. | a        | b          | c        | d          |
  7. +----------+------------+----------+------------+
  8. | %m.%d.%Y | 2003-10-31 | %d.%m.%Y | 03.10.2003 |
  9. +----------+------------+----------+------------+
  10. 1 row in set (0.00 sec)


LAST_DAY()：返回某个日期的该月最后一天的日期
  1. mysql> SELECT LAST_DAY('2000-02-02'), LAST_DAY('2001-02-03'), LAST_DAY('2000-01-01 00:01:02'), LAST_DAY('2000-00-03'), LAST_DAY('2000-01-33');
  2. +------------------------+------------------------+---------------------------------+------------------------+------------------------+
  3. | LAST_DAY('2000-02-02') | LAST_DAY('2001-02-03') | LAST_DAY('2000-01-01 00:01:02') | LAST_DAY('2000-00-03') | LAST_DAY('2000-01-33') |
  4. +------------------------+------------------------+---------------------------------+------------------------+------------------------+
  5. | 2000-02-29             | 2001-02-28             | 2000-01-31                      | NULL                   | NULL                   |
  6. +------------------------+------------------------+---------------------------------+------------------------+------------------------+
  7. 1 row in set, 2 warnings (0.00 sec)

  9. mysql> SHOW WARNINGS;
  10. +---------+------+----------------------------------------+
  11. | Level   | Code | Message                                |
  12. +---------+------+----------------------------------------+
  13. | Warning | 1292 | Incorrect datetime value: '2000-00-03' |
  14. | Warning | 1292 | Incorrect datetime value: '2000-01-33' |
  15. +---------+------+----------------------------------------+
  16. 2 rows in set (0.00 sec)


MAKEDATE()：返回某一年的第多少天的具体日期
MAKETIME()：根据给定参数返回时间（h的上限为838，m的上限为60，s的上限为60，超过上限返回null）
  1. mysql> SELECT MAKEDATE(2000,32) a,
  2.     -> MAKEDATE(2000,366) b,
  3.     -> MAKEDATE(2000,367) c,
  4.     -> MAKEDATE(2000,0) d,
  5.     -> MAKETIME(11,22,30) e,
  6.     -> MAKETIME(25,11,11) f;
  7. +------------+------------+------------+------+----------+----------+
  8. | a          | b          | c          | d    | e        | f        |
  9. +------------+------------+------------+------+----------+----------+
  10. | 2000-02-01 | 2000-12-31 | 2001-01-01 | NULL | 11:22:30 | 25:11:11 |
  11. +------------+------------+------------+------+----------+----------+
  12. 1 row in set (0.00 sec)


MONTHNAME()：返回月份的名字
  1. mysql> SELECT MONTHNAME(NOW());
  2. +------------------+
  3. | MONTHNAME(NOW()) |
  4. +------------------+
  5. | September        |
  6. +------------------+
  7. 1 row in set (0.00 sec)


PERIOD_ADD()：增加x个月后并返回，格式是YYM或YYYYMM
  1. mysql> SELECT PERIOD_ADD(200001,5),
  2.     -> PERIOD_ADD(200808,10);
  3. +----------------------+-----------------------+
  4. | PERIOD_ADD(200001,5) | PERIOD_ADD(200808,10) |
  5. +----------------------+-----------------------+
  6. | 200006               | 200906                |
  7. +----------------------+-----------------------+
  8. 1 row in set (0.00 sec)


PERIOD_DIFF()：返回两个日期相差的月份，格式YYYYMM
  1. mysql> SELECT PERIOD_DIFF(200001,200005), PERIOD_DIFF(199912,199810);
  2. +----------------------------+----------------------------+
  3. | PERIOD_DIFF(200001,200005) | PERIOD_DIFF(199912,199810) |
  4. +----------------------------+----------------------------+
  5. | -4                         | 14                         |
  6. +----------------------------+----------------------------+
  7. 1 row in set (0.00 sec)


QUARTER()：返回日期的季度，月份1-3为1，4-6为2，7-9为3，10-12为4
  1. mysql> SELECT QUARTER('2015-01-01'), QUARTER('2015-07-01');
  2. +-----------------------+-----------------------+
  3. | QUARTER('2015-01-01') | QUARTER('2015-07-01') |
  4. +-----------------------+-----------------------+
  5. | 1                     | 3                     |
  6. +-----------------------+-----------------------+
  7. 1 row in set (0.00 sec)


SEC_TO_TIME()：将数字转换成时分秒
  1. mysql> SELECT SEC_TO_TIME(1),
  2.     -> SEC_TO_TIME(1) +0,
  3.     -> SEC_TO_TIME(3600);
  4. +----------------+-------------------+-------------------+
  5. | SEC_TO_TIME(1) | SEC_TO_TIME(1) +0 | SEC_TO_TIME(3600) |
  6. +----------------+-------------------+-------------------+
  7. | 00:00:01       | 1                 | 01:00:00          |
  8. +----------------+-------------------+-------------------+
  9. 1 row in set (0.00 sec)


SUBTIME()：返回两个时间相减的结果
  1. mysql> SELECT SUBTIME('2000-01-02 23:59:59.000003','2 3:58:58.000002') a,
  2.     -> SUBTIME('01:02:03.123456','02:02:02.123465') b;
  3. +----------------------------+------------------+
  4. | a                          | b                |
  5. +----------------------------+------------------+
  6. | 1999-12-31 20:01:01.000001 | -00:59:59.000009 |
  7. +----------------------------+------------------+
  8. 1 row in set (0.00 sec)


TIME_FORMAT()：返回格式化后的日期，若包含小时的部分大于23，%k为小时格式说明符，产生的小时值会模12。
  1. mysql> SELECT TIME_FORMAT('25:00:00', '%H %k %h %I %l'),
  2.     -> TIME_FORMAT('100:00:00', '%H %k %h %i %l');
  3. +-------------------------------------------+--------------------------------------------+
  4. | TIME_FORMAT('25:00:00', '%H %k %h %I %l') | TIME_FORMAT('100:00:00', '%H %k %h %i %l') |
  5. +-------------------------------------------+--------------------------------------------+
  6. | 25 25 01 01 1                             | 100 100 04 00 4                            |
  7. +-------------------------------------------+--------------------------------------------+
  8. 1 row in set (0.00 sec)


TIME_TO_SEC()：
  1. mysql> SELECT TIME_TO_SEC('23:59:59'), TIME_TO_SEC('00:01:01');
  2. +-------------------------+-------------------------+
  3. | TIME_TO_SEC('23:59:59') | TIME_TO_SEC('00:01:01') |
  4. +-------------------------+-------------------------+
  5. | 86399                   | 61                      |
  6. +-------------------------+-------------------------+
  7. 1 row in set (0.00 sec)


TIME()：返回时间值
  1. mysql> SELECT NOW(), TIME(NOW());
  2. +---------------------+-------------+
  3. | NOW()               | TIME(NOW()) |
  4. +---------------------+-------------+
  5. | 2015-10-09 15:11:49 | 15:11:49    |
  6. +---------------------+-------------+
  7. 1 row in set (0.00 sec)


TIMESTAMPADD()：时间戳相加
  1. mysql> SELECT TIMESTAMPADD(MINUTE,1,'2015-01-01 00:59:00') a,
  2.     -> TIMESTAMPADD(WEEK,2,'2000-01-01') b,
  3.     -> TIMESTAMPADD(SECOND,66,'2000-01-01') c;
  4. +---------------------+------------+---------------------+
  5. | a                   | b          | c                   |
  6. +---------------------+------------+---------------------+
  7. | 2015-01-01 01:00:00 | 2000-01-15 | 2000-01-01 00:01:06 |
  8. +---------------------+------------+---------------------+
  9. 1 row in set (0.00 sec)


TIMESTAMPDIFF()：时间戳相减
  1. mysql> SELECT TIMESTAMPDIFF(MINUTE,'2015-01-01 00:01:00','2015-01-01 00:00:00') a;
  2. +------+
  3. | a    |
  4. +------+
  5. | -1   |
  6. +------+
  7. 1 row in set (0.00 sec)


TO_DAYS()：返回天数，从0年开始（0000-00-00）
  1. mysql> SELECT TO_DAYS('20000101') a,    -- 这样的写法和'2000-01-01'都可以
  2.     -> TO_DAYS('0000-01-01') b,
  3.     -> TO_DAYS('0000-00-00') c;
  4. +--------+------+------+
  5. | a      | b    | c    |
  6. +--------+------+------+
  7. | 730485 | 1    | NULL |
  8. +--------+------+------+
  9. 1 row in set, 1 warning (0.00 sec)

  11. mysql> SHOW WARNINGS;
  12. +---------+------+----------------------------------------+
  13. | Level   | Code | Message                                |
  14. +---------+------+----------------------------------------+
  15. | Warning | 1292 | Incorrect datetime value: '0000-00-00' |
  16. +---------+------+----------------------------------------+
  17. 1 row in set (0.00 sec)


TO_SECONDS()：返回秒数，从0年开始（0000-00-00）
  1. mysql> SELECT TO_SECONDS('20000101') a,
  2.     -> TO_SECONDS('00000101') b,
  3.     -> TO_SECONDS('11:11:11') c,
  4.     -> TO_SECONDS('2015-10-09 11:11:11') d;
  5. +-------------+-------+-------------+-------------+
  6. | a           | b     | c           | d           |
  7. +-------------+-------+-------------+-------------+
  8. | 63113904000 | 86400 | 63488188800 | 63611608271 |
  9. +-------------+-------+-------------+-------------+
  10. 1 row in set (0.00 sec)


UNIX_TIMESTAMP()：
  1. mysql> SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP('1970-01-02 00:00:00');
  2. +------------------+-----------------------+---------------------------------------+
  3. | UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | UNIX_TIMESTAMP('1970-01-02 00:00:00') |
  4. +------------------+-----------------------+---------------------------------------+
  5. | 1444376257       | 1444376257            | 57600                                 |
  6. +------------------+-----------------------+---------------------------------------+
  7. 1 row in set (0.00 sec)


UTC_DATE()：
  1. mysql> SELECT UTC_DATE(), UTC_DATE() + 1;
  2. +------------+----------------+
  3. | UTC_DATE() | UTC_DATE() + 1 |
  4. +------------+----------------+
  5. | 2015-10-09 | 20151010       |
  6. +------------+----------------+
  7. 1 row in set (0.00 sec)


UTC_TIME()：
  1. mysql> SELECT UTC_TIME(), UTC_TIME() + 60, UTC_TIME + 0;   
  2. +------------+-----------------+--------------+
  3. | UTC_TIME() | UTC_TIME() + 60 | UTC_TIME + 0 |
  4. +------------+-----------------+--------------+
  5. | 07:42:54   | 74314           | 74254        |
  6. +------------+-----------------+--------------+
  7. 1 row in set (0.00 sec)


UTC_TIMESTAMP()：
  1. mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
  2. +---------------------+---------------------+
  3. | UTC_TIMESTAMP()     | UTC_TIMESTAMP() + 0 |
  4. +---------------------+---------------------+
  5. | 2015-10-09 07:44:48 | 20151009074448      |
  6. +---------------------+---------------------+
  7. 1 row in set (0.00 sec)


WEEK()与WEEKOFYEAR()：返回日期是该年的第几个星期（但WEEKOFYEAR()没有模式选择）。
由于习惯不同，有8种模式。
Mode First day of week Range Week 1 is the first week …
0 Sunday 0-53 with a Sunday in this year
1 Monday 0-53 with 4 or more days this year
2 Sunday 1-53 with a Sunday in this year
3 Monday 1-53 with 4 or more days this year
4 Sunday 0-53 with 4 or more days this year
5 Monday 0-53 with a Monday in this year
6 Sunday 1-53 with 4 or more days this year
7 Monday 1-53 with a Monday in this year
  1. mysql> select week('2015-01-08',5);
  2. +----------------------+
  3. | week('2015-01-08',5) |
  4. +----------------------+
  5. | 1                    |
  6. +----------------------+
  7. 1 row in set (0.00 sec)

  9. mysql> select week('2015-01-08',1);
  10. +----------------------+
  11. | week('2015-01-08',1) |
  12. +----------------------+
  13. | 2                    |
  14. +----------------------+
  15. 1 row in set (0.00 sec)


WEEKDAY()：返回星期的索引；0-周一，1-周二，2-周三，3-周四，4-周五，5-周六，6-周日
  1. mysql> SELECT WEEKDAY('2015-10-09');
  2. +-----------------------+
  3. | WEEKDAY('2015-10-09') |
  4. +-----------------------+
  5. | 4                     |
  6. +-----------------------+
  7. 1 row in set (0.00 sec)


YEARWEEK()：返回日期是哪一年的第几个星期
  1. mysql> \! cal 1 2015
  2.     January 2015
  3. Su Mo Tu We Th Fr Sa
  4.              1  2  3
  5.  4  5  6  7  8  9 10
  6. 11 12 13 14 15 16 17
  7. 18 19 20 21 22 23 24
  8. 25 26 27 28 29 30 31

  10. mysql> SELECT YEARWEEK('2015-01-03'), YEARWEEK('2015-01-04');
  11. +------------------------+------------------------+
  12. | YEARWEEK('2015-01-03') | YEARWEEK('2015-01-04') |
  13. +------------------------+------------------------+
  14. | 201452                 | 201501                 |
  15. +------------------------+------------------------+
  16. 1 row in set (0.00 sec)

作者公众号（持续更新）

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29773961/viewspace-1808967/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： MySQL int(11)及int(M)解析
下一篇： 【MySQL】慢日志大量生成 long-query-time “失效”问题
请登录后发表评论 登录
全部评论
神谕丶
微信公众号：神谕的暗影长廊

注册时间：2014-07-28

  • 博文量
    161
  • 访问量
    1195299

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593