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)
