MySQL 日期和時間函數

By:
On: ,Modified:
In: MySQL
Tagged: ,
With: 1 Comment
點閱人數：3,659次

2013-05-08_085404

這是MySQL 5實力養成暨評量裡的5-17.『QUARTER函數可計算輸入日期屬於該年的第幾季，試問執行SELECT QUARTER(‘2006-11-15’);會得到何種結果？

答案：(D)4

在程式的世界中滿麻煩的一件事就是數字的開頭是從0開始還是從1開始，有的語言陣列從0開始，有的從1開始，在資料庫的世界中，像小時是從0，但是這一題呢，卻是從1開始，所以逼得我們還是得提著腦袋去記才行，在MySQL YEARWEEK這裡我們已經檢討了有關YEARWEEK的函數，看起來MySQL的日期和時間的函數，認證考裡是必考的，只是考哪一個函數而已，這回我們就加強學習吧。

在MySQL 5.7 Reference Manual :: 12 Functions and Operators :: 12.7 Date and Time Functions

和簡體手冊 MySQL 5.1参考手册 :: 12. 函数和操作符::12.5. 日期和时间函数

轉譯成繁體中文如下：

本章論述了一些可用於操作時間值的函數。關於每個時間和日期類型具有的值域及指定值的有效格式，請參見11.3節，「日期和時間類型」。

下面的例子使用了時間函數。以下詢問選擇了最近的 30天內所有帶有date_col 值的記錄：

mysql> SELECT something FROM tbl_name

-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

注意，這個詢問也能選擇將來的日期記錄。

用於日期值的函數通常會接受時間日期值而忽略時間部分。而用於時間值的函數通常接受時間日期值而忽略日期部分。

返回各自當前日期或時間的函數在每次詢問執行開始時計算一次。這意味著在一個單一詢問中，對諸如NOW()的函數多次訪問總是會得到同樣的結果(未達到我們的目的，單一詢問也包括對儲存程式或觸發器和被該程式/觸發器使用的所有子程式的使用 )。這項原則也適用於 CURDATE()、 CURTIME()、 UTC_DATE()、UTC_TIME()、UTC_TIMESTAMP()，以及所有和它們意義相同的函數。

CURRENT_TIMESTAMP()、 CURRENT_TIME()、 CURRENT_DATE()以及FROM_UNIXTIME()函數返回連接當前時區內的值，這個值可用作time_zone系統變數的值。此外， UNIX_TIMESTAMP() 假設其參數為一個當前時區的時間日期值。請參見5.10.8節，「MySQL伺服器時區支援」。

以下函數的論述中返回值的範圍會請求完全日期。 若一個日期為「零」 值，或者是一個諸如’2001-11-00′之類的不完全日期， 提取部分日期值的函數可能會返回 0。 例如， DAYOFMONTH(’2001-11-00′) 會返回0。

  • ADDDATE(date,INTERVAL expr type) ADDDATE(expr,days)

當被第二個參數的INTERVAL格式激活後， ADDDATE()就是DATE_ADD()的同義詞。相關函數SUBDATE() 則是DATE_SUB()的同義詞。對於INTERVAL參數上的訊息 ，請參見關於DATE_ADD()的論述。

mysql> SELECT DATE_ADD(’1998-01-02′, INTERVAL 31 DAY);

-> ’1998-02-02′

mysql> SELECT ADDDATE(’1998-01-02′, INTERVAL 31 DAY);

-> ’1998-02-02′

若 days 參數只是整數值，則 MySQL 5.1將其作為天數值新增至 expr。

mysql> SELECT ADDDATE(’1998-01-02′, 31);

-> ’1998-02-02′

  • ADDTIME(expr,expr2)

ADDTIME()將 expr2新增至expr 然後返回結果。 expr 是一個時間或時間日期資料表達式，而expr2 是一個時間資料表達式。

mysql> SELECT ADDTIME(’1997-12-31 23:59:59.999999′,

->                ’1 1:1:1.000002′);

-> ’1998-01-02 01:01:01.000001′

mysql> SELECT ADDTIME(’01:00:00.999999′, ’02:00:00.999998′);

-> ’03:00:01.999997′

  • CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ() 將時間日期值dt 從from_tz 給出的時區轉到to_tz給出的時區，然後返回結果值。關於可能指定的時區的詳細論述，請參見5.10.8節，「MySQL伺服器時區支援」。若自變數無效，則這個函數會返回 NULL。

在從若from_tz 到UTC的轉化過程中，該值超出 TIMESTAMP 類型的被支援範圍，那麼轉化不會發生。關於TIMESTAMP 範圍的論述，請參見11.1.2節，「日期和時間類型概述」。

mysql> SELECT CONVERT_TZ(’2004-01-01 12:00:00′,’GMT’,’MET’);

-> ’2004-01-01 13:00:00′

mysql> SELECT CONVERT_TZ(’2004-01-01 12:00:00′,’+00:00′,’+10:00′);

-> ’2004-01-01 22:00:00′

註釋：若要使用諸如 ’MET’或 ’Europe/Moscow’之類的指定時間區，首先要設置正確的時區資料表。詳細說明見5.10.8節，「MySQL伺服器時區支援」。

  • CURDATE()

將當前日期按照’YYYY-MM-DD’ 或YYYYMMDD 格式的值返回，具體格式根據函數用在字串或是數字語境中而定。

mysql> SELECT CURDATE();

-> ’1997-12-15′

mysql> SELECT CURDATE() + 0;

-> 19971215

  • CURRENT_DATE CURRENT_DATE()

CURRENT_DATE和CURRENT_DATE()是同義詞.

  • CURTIME()

將當前時間以’HH:MM:SS’或 HHMMSS 的格式返回， 具體格式根據函數用在字串或是數字語境中而定。

mysql> SELECT CURTIME();

-> ’23:50:26′

mysql> SELECT CURTIME() + 0;

-> 235026

  • CURRENT_TIME, CURRENT_TIME()

CURRENT_TIME 和CURRENT_TIME() 是CURTIME()的同義詞。

  • CURRENT_TIMESTAMP, CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP和 CURRENT_TIMESTAMP()是NOW()的同義詞。

  • DATE(expr)

提取日期或時間日期資料表達式expr中的日期部分。

mysql> SELECT DATE(’2003-12-31 01:02:03′);

-> ’2003-12-31′

  • DATEDIFF(expr,expr2)

DATEDIFF() 返回起始時間 expr和結束時間expr2之間的天數。Expr和expr2 為日期或 date-and-time 資料表達式。計算中只用到這些值的日期部分。

mysql> SELECT DATEDIFF(’1997-12-31 23:59:59′,’1997-12-30′);

-> 1

mysql> SELECT DATEDIFF(’1997-11-30 23:59:59′,’1997-12-31′);

-> -31

  • DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type)

這些函數執行日期運算。 date 是一個 DATETIME 或DATE值，用來指定起始時間。 expr 是一個資料表達式，用來指定從起始日期新增或減去的時間間隔值。  Expr是一個字串;對於負值的時間間隔，它可以以一個 『-』開頭。 type 為關鍵詞，它指示了資料表達式被解釋的方式。

關鍵詞INTERVA及 type 分類符均不區分大小寫。

以下資料表顯示了type 和expr 參數的關係：

type 值 預期的 expr 格式
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND ‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND ‘MINUTES.MICROSECONDS’
MINUTE_SECOND ‘MINUTES:SECONDS’
HOUR_MICROSECOND ‘HOURS.MICROSECONDS’
HOUR_SECOND ‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE ‘HOURS:MINUTES’
DAY_MICROSECOND ‘DAYS.MICROSECONDS’
DAY_SECOND ‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE ‘DAYS HOURS:MINUTES’
DAY_HOUR ‘DAYS HOURS’
YEAR_MONTH ‘YEARS-MONTHS’

MySQL 允許任何expr 格式中的標點分隔符。資料表中所顯示的是建議的 分隔符。若 date 參數是一個 DATE值，而您的計算只會包括 YEAR、MONTH和DAY部分(即, 沒有時間部分), 其結果是一個DATE 值。否則，結果將是一個 DATETIME值。

若位於另一端的資料表達式是一個日期或日期時間值 ， 則INTERVAL expr type只允許在 + 操作符的兩端。對於 –操作符，  INTERVAL expr type 只允許在其右端，原因是從一個時間間隔中提取一個日期或日期時間值是毫無意義的。 (見下面的例子）。

mysql> SELECT ’1997-12-31 23:59:59′ + INTERVAL 1 SECOND;

-> ’1998-01-01 00:00:00′

mysql> SELECT INTERVAL 1 DAY + ’1997-12-31′;

-> ’1998-01-01′

mysql> SELECT ’1998-01-01′ – INTERVAL 1 SECOND;

-> ’1997-12-31 23:59:59′

mysql> SELECT DATE_ADD(’1997-12-31 23:59:59′,

->                 INTERVAL 1 SECOND);

-> ’1998-01-01 00:00:00′

mysql> SELECT DATE_ADD(’1997-12-31 23:59:59′,

->                 INTERVAL 1 DAY);

-> ’1998-01-01 23:59:59′

mysql> SELECT DATE_ADD(’1997-12-31 23:59:59′,

->                 INTERVAL ’1:1′ MINUTE_SECOND);

-> ’1998-01-01 00:01:00′

mysql> SELECT DATE_SUB(’1998-01-01 00:00:00′,

->                 INTERVAL ’1 1:1:1′ DAY_SECOND);

-> ’1997-12-30 22:58:59′

mysql> SELECT DATE_ADD(’1998-01-01 00:00:00′,

->                 INTERVAL ‘-1 10′ DAY_HOUR);

-> ’1997-12-30 14:00:00′

mysql> SELECT DATE_SUB(’1998-01-02′, INTERVAL 31 DAY);

-> ’1997-12-02′

mysql> SELECT DATE_ADD(’1992-12-31 23:59:59.000002′,

->            INTERVAL ’1.999999′ SECOND_MICROSECOND);

-> ’1993-01-01 00:00:01.000001′

若您指定了一個過於短的時間間隔值 (不包括type 關鍵詞所預期的所有時間間隔部分), MySQL 假定您已經省去了時間間隔值的最左部分。 例如，您指定了一種類型的DAY_SECOND, expr 的值預期應當具有天、 小時、分鐘和秒部分。若您指定了一個類似 ’1:10′的值, MySQL 假定天和小時部分不存在，那麼這個值代資料表分和秒。換言之, ’1:10′ DAY_SECOND 被解釋為相當於 ’1:10′ MINUTE_SECOND。這相當於 MySQL將TIME 值解釋為所耗費的時間而不是日時的解釋方式。

假如您對一個日期值新增或減去一些含有時間部分的內容，則結果自動轉化為一個日期時間值：

mysql> SELECT DATE_ADD(’1999-01-01′, INTERVAL 1 DAY);

-> ’1999-01-02′

mysql> SELECT DATE_ADD(’1999-01-01′, INTERVAL 1 HOUR);

-> ’1999-01-01 01:00:00′

假如您使用了格式嚴重錯誤的日期，則結果為 NULL。假如您新增了  MONTH、YEAR_MONTH或YEAR ，而結果日期中有一天的日期大於新增的月份的日期最大限度，則這個日期自動被調整為新增月份的最大日期：

mysql> SELECT DATE_ADD(’1998-01-30′, INTERVAL 1 MONTH);

-> ’1998-02-28′

  • DATE_FORMAT(date,format)

根據format 字串安排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允許儲存諸如 ’2004-00-00′的不完全日期。

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
        -> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%D %y %a %d %m %b %j');
        -> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
  • DAY(date)

DAY() 和DAYOFMONTH()的意義相同。

  • DAYNAME(date)

返回date 對應的工作日名稱。

mysql> SELECT DAYNAME(’1998-02-05′);

-> ‘週四’

  • DAYOFMONTH(date)

返回date 對應的該月日期，範圍是從 1到31。

mysql> SELECT DAYOFMONTH(’1998-02-03′);

-> 3

  • DAYOFWEEK(date)

返回date (1 = 週日, 2 = 週一, …, 7 = 週六)對應的工作日索引。這些索引值符合 ODBC標準。

mysql> SELECT DAYOFWEEK(’1998-02-03′);

-> 3

  • DAYOFYEAR(date)

返回date 對應的一年中的天數，範圍是從 1到366。

mysql> SELECT DAYOFYEAR(’1998-02-03′);

-> 34

  • EXTRACT(type FROM date)

EXTRACT()函數所使用的時間間隔類型說明符同 DATE_ADD()或DATE_SUB()的相同，但它從日期中提取其部分，而不是執行日期運算。

mysql> SELECT EXTRACT(YEAR FROM ’1999-07-02′);

-> 1999

mysql> SELECT EXTRACT(YEAR_MONTH FROM ’1999-07-02 01:02:03′);

-> 199907

mysql> SELECT EXTRACT(DAY_MINUTE FROM ’1999-07-02 01:02:03′);

-> 20102

mysql> SELECT EXTRACT(MICROSECOND

->                FROM ’2003-01-02 10:30:00.00123′);

-> 123

  • FROM_DAYS(N)

給定一個天數  N, 返回一個DATE值。

mysql> SELECT FROM_DAYS(729669);

-> ’1997-10-07′

使用 FROM_DAYS()處理古老日期時，務必謹慎。他不用於處理陽曆出現前的日期(1582)。請參見12.6節，「MySQL使用什麼日曆？」。

  • FROM_UNIXTIME(unix_timestamp) , FROM_UNIXTIME(unix_timestamp,format)

返回’YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS 格式值的unix_timestamp參數資料表示，具體格式取決於該函數是否用在字串中或是數字語境中。

若format 已經給出，則結果的格式是根據format 字串而定。 format 可以包含同DATE_FORMAT() 函數輸入項列資料表中相同的說明符。

mysql> SELECT FROM_UNIXTIME(875996580);

-> ’1997-10-04 22:23:00′

mysql> SELECT FROM_UNIXTIME(875996580) + 0;

-> 19971004222300

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),

->                      ‘%Y %D %M %h:%i:%s %x’);

-> ’2003 6th August 06:22:58 2003′

  • GET_FORMAT(DATE|TIME|DATETIME, ‘EUR’|’USA’|’JIS’|’ISO’|’INTERNAL’)

返回一個格式字串。這個函數在同DATE_FORMAT() 及STR_TO_DATE()函數結合時很有用。

第一個參數的3個可能值和第二個參數的5個可能值產生 15 個可能格式字串 (對於使用的說明符，請參見DATE_FORMAT()函數說明資料表 )。

函數使用 結果
GET_FORMAT(DATE,’USA’) ‘%m.%d.%Y’
GET_FORMAT(DATE,’JIS’) ‘%Y-%m-%d’
GET_FORMAT(DATE,’ISO’) ‘%Y-%m-%d’
GET_FORMAT(DATE,’EUR’) ‘%d.%m.%Y’
GET_FORMAT(DATE,’INTERNAL’) ‘%Y%m%d’
GET_FORMAT(DATETIME,’USA’) ‘%Y-%m-%d-%H.%i.%s’
GET_FORMAT(DATETIME,’JIS’) ‘%Y-%m-%d %H:%i:%s’
GET_FORMAT(DATETIME,’ISO’) ‘%Y-%m-%d %H:%i:%s’
GET_FORMAT(DATETIME,’EUR’) ‘%Y-%m-%d-%H.%i.%s’
GET_FORMAT(DATETIME,’INTERNAL’) ‘%Y%m%d%H%i%s’
GET_FORMAT(TIME,’USA’) ‘%h:%i:%s %p’
GET_FORMAT(TIME,’JIS’) ‘%H:%i:%s’
GET_FORMAT(TIME,’ISO’) ‘%H:%i:%s’
GET_FORMAT(TIME,’EUR’) ‘%H.%i.%S’
GET_FORMAT(TIME,’INTERNAL’) ‘%H%i%s’

ISO 格式為ISO 9075, 而非ISO 8601.

也可以使用TIMESTAMP, 這時GET_FORMAT()的返回值和DATETIME相同。

mysql> SELECT DATE_FORMAT(’2003-10-03′,GET_FORMAT(DATE,’EUR’));

-> ’03.10.2003′

mysql> SELECT STR_TO_DATE(’10.31.2003′,GET_FORMAT(DATE,’USA’));

-> ’2003-10-31′

或見13.5.3節，「SET語法」。

  • HOUR(time)

返回time 對應的小時數。對於日時值的返回值範圍是從 0 到 23 。

mysql> SELECT HOUR(’10:05:03′);

-> 10

然而,  TIME 值的範圍實際上非常大, 所以HOUR可以返回大於23的值。

mysql> SELECT HOUR(’272:59:59′);

-> 272

  • LAST_DAY(date)

獲取一個日期或日期時間值，返回該月最後一天對應的值。若參數無效，則返回NULL。

mysql> SELECT LAST_DAY(’2003-02-05′);

-> ’2003-02-28′

mysql> SELECT LAST_DAY(’2004-02-05′);

-> ’2004-02-29′

mysql> SELECT LAST_DAY(’2004-01-01 01:01:01′);

-> ’2004-01-31′

mysql> SELECT LAST_DAY(’2003-03-32′);

-> NULL

  • LOCALTIME, LOCALTIME()

LOCALTIME 及 LOCALTIME()和NOW()具有相同意義。

  • LOCALTIMESTAMP, LOCALTIMESTAMP()

LOCALTIMESTAMP和LOCALTIMESTAMP()和NOW()具有相同意義。

  • MAKEDATE(year,dayofyear)

給出年份值和一年中的天數值，返回一個日期。dayofyear 必須大於 0 ，否則結果為 NULL。

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);

-> ’2001-01-31′, ’2001-02-01′

mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);

-> ’2001-12-31′, ’2004-12-30′

mysql> SELECT MAKEDATE(2001,0);

-> NULL

  • MAKETIME(hour,minute,second)

返回由hour、 minute和second 參數計算得出的時間值。

mysql> SELECT MAKETIME(12,15,30);

-> ’12:15:30′

  • MICROSECOND(expr)

從時間或日期時間資料表達式expr返回微秒值，其數字範圍從 0到 999999。

mysql> SELECT MICROSECOND(’12:00:00.123456′);

-> 123456

mysql> SELECT MICROSECOND(’1997-12-31 23:59:59.000010′);

-> 10

  • MINUTE(time)

返回 time 對應的分鐘數,範圍是從 0 到 59。

mysql> SELECT MINUTE(’98-02-03 10:05:03′);

-> 5

  • MONTH(date)

返回date 對應的月份，範圍時從 1 到 12。

mysql> SELECT MONTH(’1998-02-03′);

-> 2

  • MONTHNAME(date)

返回date 對應月份的全名。

mysql> SELECT MONTHNAME(’1998-02-05′);

-> ‘February ‘

  • NOW()

返回當前日期和時間值，其格式為 ’YYYY-MM-DD HH:MM:SS’ 或YYYYMMDDHHMMSS ， 具體格式取決於該函數是否用在字串中或數字語境中。

mysql> SELECT NOW();

-> ’1997-12-15 23:50:26′

mysql> SELECT NOW() + 0;

-> 19971215235026

在一個儲存程式或觸發器內，NOW() 返回一個常數時間，該常數指示了該程式或觸發語句開始執行的時間。這同SYSDATE()的運行有所不同。

  • PERIOD_ADD(P,N)

新增 N 個月至週期P (格式為YYMM 或YYYYMM)，返回值的格式為 YYYYMM。注意週期參數 P 不是日期值。

mysql> SELECT PERIOD_ADD(9801,2);

-> 199803

  • PERIOD_DIFF(P1,P2)

返回週期P1和 P2 之間的月份數。P1 和P2 的格式應該為YYMM或YYYYMM。注意週期參數 P1和P2 不是日期值。

mysql> SELECT PERIOD_DIFF(9802,199703);

-> 11

  • QUARTER(date)

返回date 對應的一年中的季度值，範圍是從 1到 4。

mysql> SELECT QUARTER(’98-04-01′);

-> 2

  • SECOND(time)

返回time 對應的秒數, 範圍是從 0到59。

mysql> SELECT SECOND(’10:05:03′);

-> 3

  • SEC_TO_TIME(seconds)

返回被轉化為小時、 分鐘和秒數的seconds參數值，其格式為 ’HH:MM:SS’ 或HHMMSS，具體格式根據該函數是否用在字串或數字語境中而定。

mysql> SELECT SEC_TO_TIME(2378);

-> ’00:39:38′

mysql> SELECT SEC_TO_TIME(2378) + 0;

-> 3938

  • STR_TO_DATE(str,format)

這是DATE_FORMAT() 函數的倒轉。它獲取一個字串 str 和一個格式字串format。若格式字串包含日期和時間部分，則 STR_TO_DATE()返回一個 DATETIME 值， 若該字串只包含日期部分或時間部分，則返回一個 DATE或TIME值。

str所包含的日期、時間或日期時間值應該在format指示的格式中被給定。對於可用在format中的說明符，請參見DATE_FORMAT() 函數說明資料表。 所有其它的字元被逐字獲取，因此不會被解釋。若 str 包含一個非法日期、時間或日期時間值，則 STR_TO_DATE()返回NULL。同時，一個非法值會引起警告。

對日期值部分的範圍檢查在11.3.1節，「DATETIME、DATE和TIMESTAMP類型」有詳細說明。其意義是，例如，只要具體日期部分的範圍時從 1到 31之間，則允許一個日期中的具體日期部分大於一個月中天數值。並且，允許「零」日期或帶有0值部分的日期。

mysql> SELECT STR_TO_DATE(’00/00/0000′, ‘%m/%d/%Y’);

-> ’0000-00-00′

mysql> SELECT STR_TO_DATE(’04/31/2004′, ‘%m/%d/%Y’);

-> ’2004-04-31′

  • SUBDATE(date,INTERVAL expr type) SUBDATE(expr,days)

當被第二個參數的 INTERVAL型式使用時, SUBDATE()和DATE_SUB()的意義相同。對於有關INTERVAL參數的訊息， 見有關 DATE_ADD()的討論。

mysql> SELECT DATE_SUB(’1998-01-02′, INTERVAL 31 DAY);

-> ’1997-12-02′

mysql> SELECT SUBDATE(’1998-01-02′, INTERVAL 31 DAY);

-> ’1997-12-02′

第二個形式允許對days使用整數值。在這些情況下，它被算作由日期或日期時間資料表達式 expr中提取的天數。

mysql> SELECT SUBDATE(’1998-01-02 12:00:00′, 31);

-> ’1997-12-02 12:00:00′

注意不能使用格式 ”%X%V” 來將一個 year-week 字串轉化為一個日期，原因是當一個星期跨越一個月份界限時，一個年和星期的組合不能標示一個唯一的年和月份。若要將year-week轉化為一個日期，則也應指定具體工作日：

mysql> select str_to_date(’200442 Monday’, ‘%X%V %W’);

-> 2004-10-18

  • SUBTIME(expr,expr2)

SUBTIME()從expr 中提取expr2 ，然後返回結果。expr 是一個時間或日期時間資料表達式，而xpr2 是一個時間資料表達式。

mysql> SELECT SUBTIME(’1997-12-31 23:59:59.999999′,’1 1:1:1.000002′);

-> ’1997-12-30 22:58:58.999997′

mysql> SELECT SUBTIME(’01:00:00.999999′, ’02:00:00.999998′);

-> ‘-00:59:59.999999′

  • SYSDATE()

返回當前日期和時間值，格式為’YYYY-MM-DD HH:MM:SS’ 或YYYYMMDDHHMMSS， 具體格式根據函數是否用在字串或數字語境而定。

在一個儲存程式或觸發器中，SYSDATE()返回其執行的時間，而非儲存成都或觸發語句開始執行的時間。這個NOW()的運作有所不同。

  • TIME(expr)

提取一個時間或日期時間資料表達式的時間部分，並將其以字串形式返回。

mysql> SELECT TIME(’2003-12-31 01:02:03′);

-> ’01:02:03′

mysql> SELECT TIME(’2003-12-31 01:02:03.000123′);

-> ’01:02:03.000123′

  • TIMEDIFF(expr,expr2)

TIMEDIFF() 返回起始時間 expr 和結束時間expr2 之間的時間。 expr 和expr2 為時間或 date-and-time 資料表達式，兩個的類型必須一樣。

mysql> SELECT TIMEDIFF(’2000:01:01 00:00:00′,

->                 ’2000:01:01 00:00:00.000001′);

-> ‘-00:00:00.000001′

mysql> SELECT TIMEDIFF(’1997-12-31 23:59:59.000001′,

->                 ’1997-12-30 01:01:01.000002′);

-> ’46:58:57.999999′

  • TIMESTAMP(expr) , TIMESTAMP(expr,expr2)

對於一個單參數,該函數將日期或日期時間資料表達式 expr 作為日期時間值返回.對於兩個參數，它將時間資料表達式 expr2 新增到日期或日期時間資料表達式 expr 中，將theresult作為日期時間值返回。

mysql> SELECT TIMESTAMP(’2003-12-31′);

-> ’2003-12-31 00:00:00′

mysql> SELECT TIMESTAMP(’2003-12-31 12:00:00′,’12:00:00′);

-> ’2004-01-01 00:00:00′

  • TIMESTAMPADD(interval,int_expr,datetime_expr)

將整型資料表達式int_expr 新增到日期或日期時間資料表達式 datetime_expr中。 int_expr 的單位被時間間隔參數給定，該參數必須是以下值的其中一個： FRAC_SECOND、SECOND、 MINUTE、 HOUR、 DAY、 WEEK、MONTH、 QUARTER或 YEAR。

可使用所顯示的關鍵詞指定Interval值，或使用SQL_TSI_前綴。例如, DAY或SQL_TSI_DAY 都是正確的。

mysql> SELECT TIMESTAMPADD(MINUTE,1,’2003-01-02′);

-> ’2003-01-02 00:01:00′

mysql> SELECT TIMESTAMPADD(WEEK,1,’2003-01-02′);

-> ’2003-01-09′

  • TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)

返回日期或日期時間資料表達式datetime_expr1 和datetime_expr2the 之間的整數差。其結果的單位由interval 參數給出。interval 的法定值同TIMESTAMPADD()函數說明中所列出的相同。

mysql> SELECT TIMESTAMPDIFF(MONTH,’2003-02-01′,’2003-05-01′);

-> 3

mysql> SELECT TIMESTAMPDIFF(YEAR,’2002-05-01′,’2001-01-01′);

-> -1

  • TIME_FORMAT(time,format)

其使用和 DATE_FORMAT()函數相同，然而format 字串可能僅會包含處理小時、分鐘和秒的格式說明符。其它說明符產生一個NULL值或0。

若time value包含一個大於23的小時部分，則 %H 和 %k 小時格式說明符會產生一個大於0..23的通常範圍的值。另一個小時格式說明符產生小時值模數12。

mysql> SELECT TIME_FORMAT(’100:00:00′, ‘%H %k %h %I %l’);

-> ’100 100 04 04 4′

  • TIME_TO_SEC(time)

返回已轉化為秒的time參數。

mysql> SELECT TIME_TO_SEC(’22:23:00′);

-> 80580

mysql> SELECT TIME_TO_SEC(’00:39:38′);

-> 2378

  • TO_DAYS(date)

給定一個日期date，返回一個天數 (從年份0開始的天數 )。

mysql> SELECT TO_DAYS(950501);

-> 728779

mysql> SELECT TO_DAYS(’1997-10-07′);

-> 729669

TO_DAYS() 不用於陽曆出現(1582)前的值，原因是當日曆改變時，遺失的日期不會被考慮在內。請參見12.6節，「MySQL使用什麼日曆？」。

請記住， MySQL使用11.3節，「日期和時間類型」中的規則將日期中的二位數年份值轉化為四位。例如，  ’1997-10-07′和 ’97-10-07′ 被視為同樣的日期:

mysql> SELECT TO_DAYS(’1997-10-07′), TO_DAYS(’97-10-07′);

-> 729669, 729669

對於1582 年之前的日期(或許在其它地區為下一年 )，該函數的結果實不可靠的。詳見12.6節，「MySQL使用什麼日曆？」 。

  • UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

若無參數使用，則返回一個Unix timestamp (’1970-01-01 00:00:00′ GMT 之後的秒數) 作為無符號整數。若用date 來使用UNIX_TIMESTAMP()，它會將參數值以’1970-01-01 00:00:00′ GMT後的秒數的形式返回。date 可以是一個DATE 字串、一個 DATETIME字串、一個 TIMESTAMP或一個當地時間的YYMMDD 或YYYMMDD格式的數字。

mysql> SELECT UNIX_TIMESTAMP();

-> 882226357

mysql> SELECT UNIX_TIMESTAMP(’1997-10-04 22:23:00′);

-> 875996580

當 UNIX_TIMESTAMP被用在 TIMESTAMP列時，函數直接返回內部時戳值，  而不進行任何隱含的 「string-to-Unix-timestamp」轉化。假如您向UNIX_TIMESTAMP()傳遞一個溢出日期，它會返回 0，但請注意只有基本範圍檢查會被履行 (年份從1970 到 2037， 月份從01到12，日期從  01 到31)。

假如您想要減去 UNIX_TIMESTAMP() 列，您或許希望刪去帶符號整數的結果。請參見12.8節，「Cast函數和操作符」。

  • UTC_DATE, UTC_DATE()

返回當前 UTC日期值，其格式為 ’YYYY-MM-DD’ 或 YYYYMMDD，具體格式取決於函數是否用在字串或數字語境中。

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;

-> ’2003-08-14′, 20030814

  • UTC_TIME, UTC_TIME()

返回當前 UTC 值，其格式為  ‘HH:MM:SS’ 或HHMMSS，具體格式根據該函數是否用在字串或數字語境而定。

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;

-> ’18:07:53′, 180753

  • UTC_TIMESTAMP, UTC_TIMESTAMP()

返回當前UTC日期及時間值，格式為 ’YYYY-MM-DD HH:MM:SS’ 或YYYYMMDDHHMMSS，具體格式根據該函數是否用在字串或數字語境而定。

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;

-> ’2003-08-14 18:08:04′, 20030814180804

  • WEEK(date[,mode])

該函數返回date 對應的星期數。WEEK() 的雙參數形式允許您指定該星期是否起始於週日或週一， 以及返回值的範圍是否為從0 到53 或從1 到53。若 mode參數被省略，則使用default_week_format系統自變數的值。請參見5.3.3節，「伺服器系統變數」。

以下資料表說明了mode 參數的工作過程：d

  第一天    
Mode 工作日 範圍 Week 1 為第一周 …
0 週日 0-53 本年度中有一個週日
1 週一 0-53 本年度中有3天以上
2 週日 1-53 本年度中有一個週日
3 週一 1-53 本年度中有3天以上
4 週日 0-53 本年度中有3天以上
5 週一 0-53 本年度中有一個週一
6 週日 1-53 本年度中有3天以上
7 週一 1-53 本年度中有一個週一

mysql> SELECT WEEK(’1998-02-20′);

-> 7

mysql> SELECT WEEK(’1998-02-20′,0);

-> 7

mysql> SELECT WEEK(’1998-02-20′,1);

-> 8

mysql> SELECT WEEK(’1998-12-31′,1);

-> 53

注意，假如有一個日期位於前一年的最後一周， 若您不使用2、3、6或7作為mode 參數選擇，則MySQL返回 0：

mysql> SELECT YEAR(’2000-01-01′), WEEK(’2000-01-01′,0);

-> 2000, 0

有人或許會提出意見，認為 MySQL 對於WEEK() 函數應該返回 52 ，原因是給定的日期實際上發生在1999年的第52周。我們決定返回0作為代替的原因是我們希望該函數能返回「給定年份的星期數」。這使得WEEK() 函數在同其它從日期中抽取日期部分的函數結合時的使用更加可靠。

假如您更希望所計算的關於年份的結果包括給定日期所在周的第一天，則應使用 0、2、5或 7 作為mode參數選擇。

mysql> SELECT WEEK(’2000-01-01′,2);

-> 52

作為選擇，可使用 YEARWEEK()函數:

mysql> SELECT YEARWEEK(’2000-01-01′);

-> 199952

mysql> SELECT MID(YEARWEEK(’2000-01-01′),5,2);

-> ’52′

  • WEEKDAY(date)

返回date (0 = 週一, 1 = 週二, … 6 = 週日)對應的工作日索引  weekday index for

mysql> SELECT WEEKDAY(’1998-02-03 22:23:00′);

-> 1

mysql> SELECT WEEKDAY(’1997-11-05′);

-> 2

  • WEEKOFYEAR(date)

將該日期的陽曆周以數字形式返回，範圍是從1到53。它是一個兼容度函數，相當於WEEK(date,3)。

mysql> SELECT WEEKOFYEAR(’1998-02-20′);

-> 8

  • YEAR(date)

返回date 對應的年份，範圍是從1000到9999。

mysql> SELECT YEAR(’98-02-03′);

-> 1998

  • YEARWEEK(date), YEARWEEK(date,start)

返回一個日期對應的年或周。start參數的工作同 start參數對 WEEK()的工作相同。結果中的年份可以和該年的第一周和最後一周對應的日期參數有所不同。

mysql> SELECT YEARWEEK(’1987-01-01′);

-> 198653

注意，周數和WEEK()函數隊可選參數0或 1可能會返回的(0) w有所不同，原因是此時 WEEK() 返回給定年份的語境中的周。

感謝你看到這裡，很快就可以離開了，但最好的獎勵行動就是按一下幫我分享或留言，感恩喔～

點我分享到Facebook

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *