1.日期和時間數據類型語法

用于表示時間值的日期和時間數據類型是DATE, TIME, DATETIME, TIMESTAMP和 YEAR。

對于TIME, DATETIME和 TIMESTAMP值，MySQL支持小數秒，精度可達微秒(6位數)。要定義包含小數秒部分的列，請使用語法type_name(fsp)，其中type_name是TIME、DATETIME或TIMESTAMP，fsp是小數秒精度。例如：

fsp值(如果給定)必須在0到6的范圍內。值為0表示不存在小數部分。如果省略，則默認精度為0。(這與標準的SQL默認值6不同，是為了與以前的MySQL版本兼容。)

表中的任何TIMESTAMP或DATETIME列都可以自動初始化和更新：

●DATE

日期值，支持的范圍是'1000-01-01'到'9999-12-31'。MySQL以'YYYY-MM-DD'格式顯示DATE值，但允許使用字符串或數字將值分配給DATE列。

●DATETIME[(fsp)]

日期和時間的組合。支持的范圍是'1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999'。MySQL以'YYYY-MM-DD hh:MM:ss[.fraction]'格式顯示DATETIME值，但允許使用字符串或數字將值分配給DATETIME列。

可以給出0到6范圍內的可選fsp值，以指定小數秒精度。值為0表示不存在小數部分。如果省略，則默認精度為0。

可以使用列定義子句DEFAULT為DATETIME列自動初始化到當前日期和時間，使用列定義子句ON UPDATE為DATETIME列更新到當前日期和時間。

●TIMESTAMP[(fsp)]

時間戳。范圍是'1970-01-01 00:00:01.000000' UTC到'2038-01-19 03:14:07.999999' UTC。TIMESTAMP值存儲為自'1970-01-01 00:00:00' UTC以來的秒數。它不能表示值'1970-01-01 00:00:00'，因為這相當于0秒，而值0保留為表示'0000-00-00 00:00:00'，TIMESTAMP的零值。

可以給出0到6范圍內的可選fsp值，以指定小數秒精度。值為0表示不存在小數部分。如果省略，則默認精度為0。

服務器處理TIMESTAMP定義的方式取決于explicit_defaults_for_timestamp系統變量值。

如果啟用了explicit_defaults_for_timestamp，則不會自動將DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP屬性分配給任何TIMESTAMP列。它們必須顯式地包含在列定義中。此外，任何未顯式聲明為NOT NULL的TIMESTAMP都允許空值。

如果禁用explicit_defaults_for_timestamp，服務器將按如下方式處理TIMESTAMP：

除非另有指定，否則表中的第一個TIMESTAMP列定義為，如果未顯式分配值，則自動設置為最近修改的日期和時間。這使得TIMESTAMP對于記錄INSERT或UPDATE操作的時間戳非常有用。

如果TIMESTAMP列沒有被定義為NULL，為它分配NULL值就相當于分配了當前日期和時間。

可以使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP列定義子句自動初始化或更新到當前日期和時間。默認情況下，如前所述，第一個TIMESTAMP列具有這些屬性。但是，表中的任何TIMESTAMP列都可以定義為具有這些屬性。

●TIME[(fsp)]

時間。范圍是'-838:59:59.000000'到'838:59:59.000000'。MySQL以'hh:mm:ss[.fraction]'格式顯示TIME值，但允許使用字符串或數字將值分配給TIME列。

可以給出0到6范圍內的可選fsp值，以指定小數秒精度。值為0表示不存在小數部分。如果省略，則默認精度為0。

●YEAR[(4)]

四位數格式的年。MySQL以YYYY格式顯示YEAR值，但允許使用字符串或數字將值分配給YEAR列。值顯示范圍從1901到2155，或者0000。

SUM()和AVG()聚合函數不能處理時間值。(它們將值轉換為數字，將丟棄第一個非數字字符之后所有內容。)若要解決此問題，請轉換為數字單位，執行聚合操作，然后轉換回時間值。示例：

2.DATE、 DATETIME 和 TIMESTAMP類型

DATE、DATETIME 和 TIMESTAMP類型是相關的。本節介紹了它們的特點、相似之處和不同之處。MySQL可以識別多種格式的DATE、DATETIME和TIMESTAMP 值。

DATE類型用于具有日期但沒有時間部分的值。MySQL以'YYYY-MM-DD'格式檢索并顯示DATE值。支持的范圍是'1000-01-01'到'9999-12-31'。

DATETIME類型用于同時包含日期和時間部分的值。MySQL以'YYYY-MM-DD hh:MM:ss'格式檢索并顯示DATETIME值。支持的范圍是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

TIMESTAMP數據類型用于包含日期和時間部分的值。TIMESTAMP的范圍是'1970-01-01 00:00:01' UTC到'2038-01-19 03:14:07' UTC。

DATETIME或TIMESTAMP值可以包含小數秒部分，最高達微秒(6位)精度。特別是，插入DATETIME或TIMESTAMP列中的任何小數部分都將被存儲而不是丟棄。包含小數部分時，這些值的格式為'YYYY-MM-DD hh:MM:ss[.fraction]'，DATETIME值的范圍為'1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999'，TIMESTAMP值的范圍為'1970-01-01 00:00:01.000000'到'2038-01-19 03:14:07.999999'。小數部分應始終與其余時間用小數點分隔；不能用其他小數秒分隔符。

MySQL存儲時將TIMESTAMP值從當前時區轉換為UTC，檢索時從UTC轉換為當前時區。(其他類型(如DATETIME)不會發生這種情況。)默認情況下，每個連接的當前時區是服務器的時間。時區可以根據每個連接進行設置。只要時區設置保持不變，就可以得到存儲的相同值。如果存儲TIMESTAMP值，然后更改時區并檢索該值，則檢索到的值與存儲的值不同。發生這種情況的原因是沒有使用同一時區在兩個方向上進行轉換。可以從系統變量 time_zone 中得到當前時區。

從MySQL 8.0.19開始，在向表中插入TIMESTAMP和DATETIME值時，可以指定時區偏移量。偏移量被附加到日期時間后面(原文可能有誤，具體參加最下方原始文檔)，不包含空格，并使用與設置 time_zone 系統變量相同的格式，但有以下例外情況：

●對于小于10的小時值，需要加前導零。

●不允許'-00:00'值。

●不能使用'EET'和'Asia/Shanghai'等時區名稱；也不能使用'SYSTEM'。

此示例演示如何使用不同的時區設置將帶有時區偏移的日期時間值插入TIMESTAMP和DATETIME列，然后檢索它們：

查詢結果中日期時間值不顯示偏移量，即使在插入時使用了偏移量。

支持的偏移量范圍為-14:00到+14:00(含)。

包含時區偏移量的日期時間字面值作為整體的參數值傳入語句。

如果SQL模式允許，則無效的DATE、DATETIME和TIMESTAMP值將轉換為對應類型的"零"值('0000-00-00'或'0000-00-00 00:00')。具體行為取決于是否啟用了嚴格SQL模式或NO_ZERO_DATE SQL模式。

MySQL中日期值特定屬性釋義：

●MySQL允許對指定為字符串的值使用"寬松"格式，其中任何標點字符都可以用作日期部分或時間部分之間的分隔符。在某些情況下，這種語法可能具有欺騙性。例如，'10:11:12'這樣的值因為 : 可能看起來像時間值，但如果在日期上下文中使用，則會被解釋為'2010-11-12'。值'10:45:15'轉換為'0000-00-00'，因為'45'不是有效的月份。

日期和時間部分與小數秒部分之間唯一可識別的分隔符是小數點。

●服務器要求月和日的值是有效的，而不僅僅是分別在1到12和1到31之間。禁用嚴格模式時，無效日期(如'2004-04-31')將轉換為'0000-00-00'，并生成警告。啟用嚴格模式時，無效日期將引發錯誤。若要允許此類日期，請啟用 ALLOW_INVALID_DATES。

●MySQL不接受在日或月值是0的TIMESTAMP值或不是有效日期的值。如果SQL模式允許，則此規則的唯一例外是特殊的零值'0000-00-00 00:00:00'。具體的行為取決于是否啟用了嚴格SQL模式和NO_ZERO_DATE SQL模式。

●包含兩位數年份的日期值不明確，因為世紀值未知。MySQL使用以下規則解釋2位數的年份值：

① 70-99之間的年份值將變為1970-1999。

② 00-69之間的年份值將變為2000-2069。

3.TIME 類型

MySQL以'hh:mm:ss'格式檢索和顯示TIME值(小時值數字大時用'hhh:mm:ss'格式)。TIME值的范圍從'-838:59:59'到'838:59:59'。小時值可能很大，因為時間類型不僅可以用來表示一天中的某個時間(必須小于24小時)，還可以表示經過的時間或兩個事件的時間間隔(可能遠遠大于24小時，甚至為負)。

MySQL可以識別多種格式的TIME值，其中一些格式可以包含最高達微秒(6位)精度的小數秒部分。特別是，插入TIME列的值中的任何小數部分都將被存儲而不是丟棄。包含小數部分時，TIME值的范圍為'-838:59:59.000000'到'838:59:59.000000'。

將縮寫值分配給TIME列時請務必當心。MySQL將帶有冒號的縮寫TIME值解釋為一天中的時間。例如，'11:12'是指'11:12:00'，而不是'00:11:12'。如果縮寫值不含冒號，MySQL使用最右邊的兩個數字表示秒(即，這個時間是一段時間而不是一天中的某個時間)。例如，您可能認為'1112'和1112表示'11:12:00'(11點12分鐘)，但MySQL將其解釋為'00:11:12'(11分鐘12秒)。類似地，'12'和12被解釋為'00:00:12'。

時間部分和小數秒部分之間唯一可識別的分隔符是小數點。

默認情況下，位于TIME范圍之外但在其他情況下有效的值將被剪裁到范圍的最近端點。例如，'-850:00:00'和'850:00:00'轉換為'-838:59:59'和'838:59:59'。無效的TIME值轉換為'00:00:00'。請注意，由于'00:00:00'本身是一個有效的TIME值，因此無法從存儲在表中的'00:00:00'值判斷原始值是指定為'00:00:00'還是一個無效值。

4.YEAR 類型

YEAR類型是一個1字節數據類型，用于表示年份值。它可以聲明為隱式顯示寬度為4個字符的年份，也可以顯式聲明顯示寬度YEAR(4)，和隱式聲明效果是等同的。

從MySQL 8.0.19開始，不推薦使用具有顯式顯示寬度的YEAR(4)數據類型，并且在以后的MySQL版本中將刪除對它的支持。請使用不帶顯示寬度的年份，其含義相同。

MySQL 8.0不支持舊版本MySQL中允許的2位數YEAR(2)數據類型。

MySQL以YYYY格式顯示YEAR值，范圍是1901到2155和0000。

YEAR接受多種格式的輸入值：

●'1901'到'2155'范圍內的4位字符串。

●1901到2155范圍內的4位數字。

●'0'到'99'范圍內的1位或2位字符串。MySQL將范圍'0'到'69'和'70'到'99'的值分別轉換為2000到2069和1970到1999之間的YEAR值。

●0到99范圍內的1位或2位數字。MySQL將范圍為1到69、70到99的值分別轉換為范圍為2001到2069和1970到1999的YEAR值。

插入數字0的結果的顯示值為0000，內部值為0000。若要插入零并將其解釋為2000，請將其指定為字符串'0'或'00'。

●YEAR可接受的函數返回結果，例如NOW()。

如果沒有啟用嚴格的SQL模式，MySQL會將無效的YEAR值轉換為0000。在嚴格的SQL模式下，嘗試插入無效的YEAR值會引發錯誤。

文章來源: blog.51cto.com，作者：數據雜貨鋪，版權歸原作者所有，如需轉載，請聯系作者。

原文鏈接：blog.51cto.com/15023289/2561177

總結

以上是生活随笔為你收集整理的mysql提供了表示日期和时间的数据类型_MySQL数据类型 - 日期和时间类型（1）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。