MySQL笔记五之 日期和时间函数
MySQL笔记五之 日期和时间函数获取当前日期的函数和获取当前时间的函数获取当前日期和时间的函数获取UNXI时间戳函数返回UTC日期的函数和返回UTC时间的函数获取月份的函数MONTHdate和MONTHNAMEdate获取星期的函数DAYNAMEdateDAYOFWEEKdate和WEEKDAYdate获取星期数WEEKd和WEEKOFYEARd获取星期数WEEKd和WEEKOFYEARd获取年份季度小时分钟和秒钟的函数获取日期的指定值的函数EXTRACTtype FROM date计算日期和时间的函数将日期和时间格式化的函数
1.获取当前日期的函数和获取当前时间的函数
mysql> SELECT CURDATE(),CURRENT_DATE(),CURDATE()+0;+------------+----------------+-------------+| CURDATE() | CURRENT_DATE() | CURDATE()+0 |+------------+----------------+-------------+| 2017-02-06 | 2017-02-06 | 20170206 |+------------+----------------+-------------+1 row in set (0.04 sec)mysql> SELECT CURTIME(),CURRENT_TIME(),CURTIME()+0;+-----------+----------------+-------------+| CURTIME() | CURRENT_TIME() | CURTIME()+0 |+-----------+----------------+-------------+| 19:42:14 | 19:42:14 | 194214 |+-----------+----------------+-------------+1 row in set (0.00 sec)总结： 1.CURDATE()和CURRENT_DATE()函数作用相同，将当前的日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。 2.CCURTIME()和CURRENT_TIME()函数作用相同，将当前的日期按照‘HH:MM:SS’或HHMMSS格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。
2.获取当前日期和时间的函数
mysql> SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();+---------------------+---------------------+---------------------+---------------------+| CURRENT_TIMESTAMP() | LOCALTIME() | NOW() | SYSDATE() |+---------------------+---------------------+---------------------+---------------------+| 2017-02-06 19:51:12 | 2017-02-06 19:51:12 | 2017-02-06 19:51:12 | 2017-02-06 19:51:12 |+---------------------+---------------------+---------------------+---------------------+1 row in set (0.00 sec)总结： CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE()4个函数的作用相同，均返回当前日期和时间值。格式为‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS，具体格式根据当前函数在字符串或数字语境中而定。
3.获取UNXI时间戳函数
mysql> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();+------------------+-----------------------+---------------------+| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW() |+------------------+-----------------------+---------------------+| 1486382315 | 1486382315 | 2017-02-06 19:58:35 |+------------------+-----------------------+---------------------+1 row in set (0.00 sec)mysql> SELECT FROM_UNIXTIME('1486382315');+-----------------------------+| FROM_UNIXTIME('1486382315') |+-----------------------------+| 2017-02-06 19:58:35 |+-----------------------------+1 row in set (0.00 sec)总结： 1.FROM_UNIXTIME(date)函数UNIX时间戳转换为普通格式的时间，与UNIX_TIMESTAMP(date)函数互为反函数。
4.返回UTC日期的函数和返回UTC时间的函数
mysql> SELECT UTC_DATE(),UTC_DATE()+0;+------------+--------------+| UTC_DATE() | UTC_DATE()+0 |+------------+--------------+| 2017-02-06 | 20170206 |+------------+--------------+1 row in set (0.00 sec)mysql> SELECT UTC_TIME(),UTC_TIME()+0;+------------+--------------+| UTC_TIME() | UTC_TIME()+0 |+------------+--------------+| 12:17:59 | 121759 |+------------+--------------+1 row in set (0.00 sec)总结： 1.UTC_DATE()函数返回当前的UTC(世界标准时间)日期值，其格式为‘YYYY-MM-DD’或YYYYMMDD，具体格式取决于函数是否用在字符串或数字语境中。 2.UTC_TIME()函数返回当前的UTC(世界标准时间)时间值，其格式为‘HH:MM:SS’或HHMMSS，具体格式取决于函数是否用在字符串或数字语境中。
5.获取月份的函数MONTH(date)和MONTHNAME(date)
mysql> SELECT MONTH('2017-02-06');+---------------------+| MONTH('2017-02-06') |+---------------------+| 2 |+---------------------+1 row in set (0.00 sec)mysql> SELECT MONTHNAME('2017-02-06');+-------------------------+| MONTHNAME('2017-02-06') |+-------------------------+| February |+-------------------------+1 row in set (0.00 sec)总结： 1.MONTH(date)函数返回date对应月份，范围从1~12. 2.MONTHNAME(date)函数返回date对应月份的英文全全名.
6.获取星期的函数DAYNAME(date)、DAYOFWEEK(date)和WEEKDAY(date)
mysql> SELECT DAYNAME('2017-02-06');+-----------------------+| DAYNAME('2017-02-06') |+-----------------------+| Monday |+-----------------------+1 row in set (0.00 sec) mysql> SELECT DAYOFWEEK('2017-02-06');+-------------------------+| DAYOFWEEK('2017-02-06') |+-------------------------+| 2 |+-------------------------+1 row in set (0.00 sec) mysql> SELECT WEEKDAY('2017-02-06'),WEEKDAY('2017-02-06 19:58:35 ');+-----------------------+---------------------------------+| WEEKDAY('2017-02-06') | WEEKDAY('2017-02-06 19:58:35 ') |+-----------------------+---------------------------------+| 0 | 0 |+-----------------------+---------------------------------+1 row in set (0.00 sec)总结： 1.DAYNAME(date)函数返回date对应的工作日的英文名。 2.DAYOFWEEK(date)返回date对应一周中的索引（位置）。1表示周日，2表示周一，…7表示周六。 3.WEEKDAY(date)返回date对应的工作日索引。0表示周一，1表示周二,…6表示周日。 4.DAYOFWEEK()和WEEKDAY()函数都是返回指定日期在一周内的位置，只是索引的编号不同。
7.获取星期数WEEK(d)和WEEKOFYEAR(d)
mysql> SELECT WEEK('2011-02-20'),WEEK('2011-02-20',0),WEEK('2011-02-20',1);+--------------------+----------------------+----------------------+| WEEK('2011-02-20') | WEEK('2011-02-20',0) | WEEK('2011-02-20',1) |+--------------------+----------------------+----------------------+| 8 | 8 | 7 |+--------------------+----------------------+----------------------+1 row in set (0.00 sec)mysql> SELECT WEEK('2011-02-20',3),WEEKOFYEAR('2011-02-20');+----------------------+--------------------------+| WEEK('2011-02-20',3) | WEEKOFYEAR('2011-02-20') |+----------------------+--------------------------+| 7 | 7 |+----------------------+--------------------------+1 row in set (0.00 sec)总结： 1.WEEK()函数查询指定日期是一年中的第几周 2.WEEK(‘2011-02-20’)使用一个参数，其第二个参数为default_week_format默认值，为0，指定一周的第一天为周日，因此和WEEK(‘2011-02-20’,0)返回结果相同。第二个参数为1，指定一周的第一天为周一，返回值为7. 3.WEEKOFYEAR(d)计算某天位于一年中的第几周，返回是从1~53.相当于WEEK(d,3)
8.获取星期数WEEK(d)和WEEKOFYEAR(d)
mysql> SELECT DAYOFYEAR('2017-02-06');+-------------------------+| DAYOFYEAR('2017-02-06') |+-------------------------+| 37 |+-------------------------+1 row in set (0.00 sec)mysql> SELECT DAYOFMONTH('2017-02-06');+--------------------------+| DAYOFMONTH('2017-02-06') |+--------------------------+| 6 |+--------------------------+1 row in set (0.00 sec)总结： 1.DAYOFYEAR(d)返回d是一年中的第几天，范围是从1~366; 2.DAYOFMONTH(d)返回d是一个月中的第几天，范围从1~31;
9.获取年份、季度、小时、分钟和秒钟的函数
mysql> SELECT YEAR('17-02-06'),YEAR('96-02-06');+------------------+------------------+| YEAR('17-02-06') | YEAR('96-02-06') |+------------------+------------------+| 2017 | 1996 |+------------------+------------------+1 row in set (0.00 sec)mysql> SELECT QUARTER('17-02-06');+---------------------+| QUARTER('17-02-06') |+---------------------+| 1 |+---------------------+1 row in set (0.00 sec)mysql> SELECT MINUTE('17-02-06 19:58:35');+-----------------------------+| MINUTE('17-02-06 19:58:35') |+-----------------------------+| 58 |+-----------------------------+1 row in set (0.00 sec)mysql> SELECT SECOND('19:58:35');+--------------------+| SECOND('19:58:35') |+--------------------+| 35 |+--------------------+1 row in set (0.00 sec)总结： 1.YEAR(date)返回date对应的年份，范围是1970~2069.（‘00-69’转换为2000-2069，‘70-99’转换为1970-1999） 2.QUARTER(date)返回date对应的一年中的季度值，范围指定日期对应的季度。 3.MINUTE(time)返回time对应的分钟数，范围是0~59。 4.SECOND(time)返回time对应的秒数，范围是0~59。
mysql> SELECT EXTRACT(YEAR FROM '2017-02-06') AS col1, -> EXTRACT(YEAR_MONTH FROM '2017-02-06 19:58:35') AS col2, -> EXTRACT(DAY_MINUTE FROM '2017-02-06 19:58:35') AS col3;+------+--------+-------+| col1 | col2 | col3 |+------+--------+-------+| 2017 | 201702 | 61958 |+------+--------+-------+1 row in set (0.07 sec)总结： 1.EXTRACT(type FROM date)函数提取日期中的一部分。 2.type值为YEAR时，只返回年值。type为YEAR_MONTH时返回年与月份。type为DAY_MINUTE时，返回日、小时和分钟值。
11.计算日期和时间的函数
mysql> SELECT DATE_ADD('2017-02-06 19:58:35',INTERVAL 1 SECOND) AS col1, -> ADDDATE('2017-02-06 19:58:35',INTERVAL 1 SECOND) AS col2, -> DATE_ADD('2017-02-06 19:58:35',INTERVAL '1:1' MINUTE_SECOND) AS col3;+---------------------+---------------------+---------------------+| col1 | col2 | col3 |+---------------------+---------------------+---------------------+| 2017-02-06 19:58:36 | 2017-02-06 19:58:36 | 2017-02-06 19:59:36 |+---------------------+---------------------+---------------------+1 row in set (0.00 sec)总结： 1.DATE_ADD()和ADDDATE()作用相同，执行日期相加操作。
mysql> SELECT DATE_SUB('2017-02-06 19:58:35',INTERVAL 1 SECOND) AS col1, -> SUBDATE('2017-02-06 19:58:35',INTERVAL 1 SECOND) AS col2, -> DATE_SUB('2017-02-06 19:58:35',INTERVAL '0 0:1:1' DAY_SECOND) AS col3;+---------------------+---------------------+---------------------+| col1 | col2 | col3 |+---------------------+---------------------+---------------------+| 2017-02-06 19:58:34 | 2017-02-06 19:58:34 | 2017-02-06 19:57:34 |+---------------------+---------------------+---------------------+1 row in set (0.00 sec)总结： 1.DATE_SUB()SUBDATE()作用相同，执行日期相减操作。 2.DATE_ADD和DATE_SUB在指定修改的时间段时，也可以为负值，负值代表相减。
mysql> SELECT ADDTIME('2000-12-31 23:59:59','1:1:1'),ADDTIME('02:02:02','02:00:00');+----------------------------------------+--------------------------------+| ADDTIME('2000-12-31 23:59:59','1:1:1') | ADDTIME('02:02:02','02:00:00') |+----------------------------------------+--------------------------------+| 2001-01-01 01:01:00 | 04:02:02 |+----------------------------------------+--------------------------------+1 row in set (0.00 sec)mysql> SELECT SUBTIME('2000-12-31 23:59:59','1:1:1'),SUBTIME('02:02:02','02:00:00');+----------------------------------------+--------------------------------+| SUBTIME('2000-12-31 23:59:59','1:1:1') | SUBTIME('02:02:02','02:00:00') |+----------------------------------------+--------------------------------+| 2000-12-31 22:58:58 | 00:02:02 |+----------------------------------------+--------------------------------+1 row in set (0.00 sec)总结： 使用ADDTIME()和SUBTIME()对时间进行加减。
mysql> SELECT DATEDIFF('2017-02-06 19:58:35','2017-02-05') AS col1, -> DATEDIFF('2017-02-06 19:58:35','2017-03-05') AS col2;+------+------+| col1 | col2 |+------+------+| 1 | -27 |+------+------+1 row in set (0.00 sec)总结：DATEDIFF()函数计算两个日期之间的间隔天数。
12.将日期和时间格式化的函数
mysql> SELECT DATE_FORMAT('2017-02-06 19:58:35','%W %M %Y') AS col1, -> DATE_FORMAT('2017-02-06 19:58:35','%D %y %a %d %m %b %j') AS col2;+----------------------+--------------------------+| col1 | col2 |+----------------------+--------------------------+| Monday February 2017 | 6th 17 Mon 06 02 Feb 037 |+----------------------+--------------------------+1 row in set (0.00 sec)mysql> SELECT DATE_FORMAT('2017-02-06 19:58:35','%H:%i:%s') AS col1, -> DATE_FORMAT('2017-02-06 19:58:35','%X %V') AS col2;+----------+---------+| col1 | col2 |+----------+---------+| 19:58:35 | 2017 06 |+----------+---------+1 row in set (0.00 sec)mysql> SELECT TIME_FORMAT('19:58:35','%H %k %h %I %l');+------------------------------------------+| TIME_FORMAT('19:58:35','%H %k %h %I %l') |+------------------------------------------+| 19 19 07 07 7 |+------------------------------------------+1 row in set (0.00 sec)总结：1.DATE_FORMAT(date,format)根据format指定的格式显示date值。
| header 1 | header 2 |
| row 1 col 1 | row 1 col 2 |
| row 2 col 1 | row 2 col 2 |
DATE_FORMAT 时间日期格式：
| 说明符 | 说明 |
| %a | 工作日的缩写名称 (Sun..Sat) |
| %b | 月份的缩写名称 (Jan..Dec) |
| %c | 月份，数字形式(0..12) |
| %D | 带有英语后缀的该月日期 (0th, 1st, 2nd, 3rd, …) |
| %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 | 工作日名称 (周日..周六) |
| %w | 一周中的每日 (0=周日..6=周六) |
| %X | 该周的年份，其中周日为每周的第一天, 数字形式,4位数;和%V同时使用 |
| %x | 该周的年份，其中周一为每周的第一天, 数字形式,4位数;和%v同时使用 |
| %Y | 年份, 数字形式,4位数 |
| %y | 年份, 数字形式 (2位数) |
| %% | ‘%’文字字符 |
mysql> SELECT GET_FORMAT(DATE,'EUR'),GET_FORMAT(DATE,'USA');+------------------------+------------------------+| GET_FORMAT(DATE,'EUR') | GET_FORMAT(DATE,'USA') |+------------------------+------------------------+| %d.%m.%Y | %m.%d.%Y |+------------------------+------------------------+1 row in set (0.00 sec)总结：GET_FORMAT()函数显示不同格式化类型下的格式字符串。
GET_FORMAT 返回的格式字符串：
| 函数 | 返回的格式字符串 | 日期与时间的示例 |
| GET_FORMAT(DATE,’EUR’) | ‘%d.%m.%Y’ | 30.03.2014 |
| GET_FORMAT(DATE,’USA’) | ‘$m.%d.%Y’ | 03.30.2014 |
| GET_FORMAT(DATE,’JIS’) | ‘%Y-%m-%d’ | 2014-03-30 |
| GET_FORMAT(DATE,’ISO’) | ‘%Y-%m-%d’ | 2014-03-30 |
| GET_FORMAT(DATE,’INTERNAL’) | ‘%Y%m%d’ | 20140330 |
| GET_FORMAT(DATETIME,’EUR’) | ‘%Y-%m-%d-%H.%i.%s’ | 2014-03-30-22.48.08 |
| GET_FORMAT(DATETIME,’USA’) | ‘%Y-%m-%d-%H.%i.%s’ | 2014-03-30-22.48.08 |
| GET_FORMAT(DATETIME,’JIS’) | ‘%Y-%m-%d %H:%i:%s’ | 2014-03-30 22:48:08 |
| GET_FORMAT(DATETIME,’ISO’) | ‘%Y-%m-%d %H:%i:%s’ | 2014-03-30 22:48:08 |
| GET_FORMAT(DATETIME,’INTERNAL’) | ‘%Y%m%d%H%i%s’ | 20140330224808 |
| GET_FORMAT(TIME,’EUR’) | ‘%H.%i.%s’ | 22.48.08 |
| GET_FORMAT(TIME,’USA’) | ‘%h:%i:%s %p’ | 10:48:08 PM |
| GET_FORMAT(TIME,’JIS’) | ‘%H:%i:%s’ | 22:48:08 |
| GET_FORMAT(TIME,’ISO’) | ‘%H:%i:%s’ | 22:48:08 |
| GET_FORMAT(TIME,’INTERNAL’) | ‘%H%i%s’ | 224808 |
在DATE_FORMAT()函数中，使用GET_FORMAT()函数返回的显示格式字符串来显示指定日期
mysql> SELECT DATE_FORMAT('2017-02-06 19:58:35',GET_FORMAT(DATE,'USA'));+-----------------------------------------------------------+| DATE_FORMAT('2017-02-06 19:58:35',GET_FORMAT(DATE,'USA')) |+-----------------------------------------------------------+| 02.06.2017 |+-----------------------------------------------------------+1 row in set (0.00 sec)
