MySQL笔记五之 日期和时间函数

MySQL笔记五之 日期和时间函数

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。

10.获取日期的指定值的函数EXTRACT(type FROM date)

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)