mysql日期類別是什么

2024-07-24 12:34:24

字體：大中小

來源：轉載

供稿：網友

　　MySQL 中有多個表示日期的數據類型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。

　　類型名稱日期格式日期范圍存儲需求
　　YEAR YYYY 1901 ~ 2155 1 個字節
　　TIME HH:MM:SS -838:59:59 ~ 838:59:59 3 個字節
　　DATE YYYY-MM-DD 1000-01-01 ~ 9999-12-3 3 個字節
　　DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 8 個字節
　　TIMESTAMP YYYY-MM-DD HH:MM:SS 1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC 4 個字節
　　YEAR 類型

　　YEAR 類型是一個單字節類型，用于表示年，在存儲時只需要 1 個字節。可以使用各種格式指定 YEAR，如下所示：

　　以 4 位字符串或者 4 位數字格式表示的 YEAR，范圍為 '1901'～'2155'。輸入格式為 'YYYY' 或者 YYYY，例如，輸入 '2010' 或 2010，插入數據庫的值均為 2010。
　　以 2 位字符串格式表示的 YEAR，范圍為 '00' 到 '99'。'00'～'69' 和 '70'～'99' 范圍的值分別被轉換為 2000～2069 和 1970～1999 范圍的 YEAR 值。'0' 與 '00' 的作用相同。插入超過取值范圍的值將被轉換為 2000。
　　以 2 位數字表示的 YEAR，范圍為 1～99。1～99 和 70～99 范圍的值分別被轉換為 2001～2069 和 1970～1999 范圍的 YEAR 值。注意，在這里 0 值將被轉換為 0000，而不是 2000。
　　提示：兩位整數范圍與兩位字符串范圍稍有不同。例如，插入 3000 年，讀者可能會使用數字格式的 0 表示 YEAR，實際上，插入數據庫的值為 0000，而不是所希望的 3000。只有使用字符串格式的 '0' 或 '00'，才可以被正確解釋為 3000，非法 YEAR值將被轉換為 0000。

　　TIME 類型

　　TIME 類型用于只需要時間信息的值，在存儲時需要 3 個字節。格式為 HH:MM:SS。HH 表示小時，MM 表示分鐘，SS 表示秒。

　　TIME 類型的取值范圍為 -838：59：59～838：59：59，小時部分如此大的原因是 TIME 類型不僅可以用于表示一天的時間（必須小于 24 小時），還可能是某個事件過去的時間或兩個事件之間的時間間隔（可大于 24 小時，或者甚至為負）。

　　可以使用各種格式指定 TIME 值，如下所示。

　　'D HH：MM：SS' 格式的字符串。還可以使用這些“非嚴格”的語法：'HH：MM：SS'、'HH：MM'、'D HH' 或 'SS'。這里的 D 表示日，可以取 0～34 之間的值。在插入數據庫時，D 被轉換為小時保存，格式為 “D*24+HH”。
　　'HHMMSS' 格式、沒有間隔符的字符串或者 HHMMSS 格式的數值，假定是有意義的時間。例如，'101112' 被理解為'10：11：12'，但是 '106112' 是不合法的（它有一個沒有意義的分鐘部分），在存儲時將變為 00：00：00。
　　提示：為 TIME 列分配簡寫值時應注意：如果沒有冒號，MySQL 解釋值時，假定最右邊的兩位表示秒。（MySQL 解釋 TIME 值為過去的時間而不是當前的時間）。例如，讀者可能認為 '1112' 和 1112 表示 11：12：00（即 11 點過 12 分鐘），但MySQL 將它們解釋為 00：11：12（即 11 分 12 秒）。同樣 '12' 和 12 被解釋為00：00：12。相反，TIME 值中如果使用冒號則肯定被看作當天的時間，也就是說，'11：12' 表示 11：12：00，而不是 00：11：12。

　　示例

　　讓我們看一下在TIME表中使用數據類型的示例。

　　首先，創建一個新表命名為tests 一個由四列：id，name，start_at，和end_at。start_at和end_at列的數據類型是TIME。

　　CREATE TABLE tests (
　　    id INT PRIMARY KEY AUTO_INCREMENT,
　　    name VARCHAR(255) NOT NULL,
　　    start_at TIME,
　　    end_at TIME
　　);
　　其次，在tests表中插入一行。

　　INSERT INTO tests(name,start_at,end_at)
　　VALUES('Test 1', '08:00:00','10:00:00');
　　第三，從tests表中查詢數據。

　　SELECT
　　    name, start_at, end_at
　　FROM
　　    tests;

　　DATE 類型

　　DATE 類型用于僅需要日期值時，沒有時間部分，在存儲時需要 3 個字節。日期格式為 'YYYY-MM-DD'，其中 YYYY 表示年，MM 表示月，DD 表示日。

　　在給 DATE 類型的字段賦值時，可以使用字符串類型或者數字類型的數據插入，只要符合 DATE 的日期格式即可。如下所示：

　　以 'YYYY-MM-DD' 或者 'YYYYMMDD' 字符中格式表示的日期，取值范圍為 '1000-01-01'～'9999-12-3'。例如，輸入 '2015-12-31' 或者 '20151231'，插入數據庫的日期為2015-12-31。
　　以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示日期，在這里YY表示兩位的年值。MySQL 解釋兩位年值的規則：'00～69' 范圍的年值轉換為 '2000~2069'，'70~99' 范圍的年值轉換為 '1970～1999'。例如，輸入 '15-12-31'，插入數據庫的日期為 2015-12-31；輸入 '991231'，插入數據庫的日期為 1999-12-31。
　　以 YYMMDD 數字格式表示的日期，與前面相似，00~69 范圍的年值轉換為 2000～2069，80～99 范圍的年值轉換為 1980～1999。例如，輸入 151231，插入數據庫的日期為 2015-12-31，輸入 991231，插入數據庫的日期為 1999-12-31。
　　使用 CURRENT_DATE 或者 NOW()，插入當前系統日期。
　　示例

　　我們來看看下面的例子。

　　首先，創建一個新表名為people，其中出生日期列為DATE數據類型。

　　CREATE TABLE people (
　　    id INT AUTO_INCREMENT PRIMARY KEY,
　　    first_name VARCHAR(50) NOT NULL,
　　    last_name VARCHAR(50) NOT NULL,
　　    birth_date DATE NOT NULL
　　);
　　接下來，在people表中插入一行。

　　INSERT INTO people(first_name,last_name,birth_date)
　　VALUES('John','Doe','1990-09-01');
　　然后，查詢數據來自people表。

　　SELECT
　　    first_name,
　　    last_name,
　　    birth_date
　　FROM
　　    people;
　　mysql日期類型是什么

　　之后，使用兩位數年份格式將數據插入people表中。

　　INSERT INTO people(first_name,last_name,birth_date)
　　VALUES('Jack','Daniel','01-09-01'),
　　      ('Lily','Bush','80-09-01');
　　在第一行中，我們使用01（范圍00-69）作為年份，因此MySQL將其轉換為2001年。在第二行中，我們使用80（范圍70-99）作為年份，MySQL將其轉換為1980年。

　　最后，我們可以從people表中查詢數據，以檢查數據是否已根據轉換規則進行轉換。

　　SELECT
　　    first_name,
　　    last_name,
　　    birth_date
　　FROM
　　    people;

　　DATETIME 類型

　　DATETIME 類型用于需要同時包含日期和時間信息的值，在存儲時需要 8 個字節。日期格式為 'YYYY-MM-DD HH:MM:SS'，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小時，MM 表示分鐘，SS 表示秒。

　　在給 DATETIME 類型的字段賦值時，可以使用字符串類型或者數字類型的數據插入，只要符合 DATETIME 的日期格式即可，如下所示。

　　以 'YYYY-MM-DD HH：MM：SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期，取值范圍為 '1000-01-01 00：00：00'～'9999-12-3 23：59：59'。例如，輸入 '2014-12-31 05：05：05' 或者 '20141231050505’，插入數據庫的 DATETIME 值都為 2014-12-31 05：05：05。
　　以 'YY-MM-DD HH：MM：SS' 或者 'YYMMDDHHMMSS' 字符串格式表示的日期，在這里 YY 表示兩位的年值。與前面相同，'00～79' 范圍的年值轉換為 '2000～2079'，'80～99' 范圍的年值轉換為 '1980～1999'。例如，輸入 '14-12-31 05：05：05'，插入數據庫的 DATETIME 為 2014-12-31 05：05：05；輸入 141231050505，插入數據庫的 DATETIME 為 2014-12-31 05：05：05。
　　以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 數字格式表示的日期和時間。例如，輸入 20141231050505，插入數據庫的 DATETIME 為 2014-12-31 05：05：05；輸入 140505050505，插入數據庫的 DATETIME 為 2014-12-31 05：05：05。
　　DATETIME值可以包括具有YYYY-MM-DD HH:MM:SS[.fraction]例如的格式的尾隨小數秒到微秒 2015-12-20 10:01:00.999999。包含小數秒精度時，DATETIME值需要更多存儲空間，如下表所示：

　　分數秒精度存儲 (字節)
　　0 0
　　1, 2 1
　　3, 4 2
　　5, 6 3
　　例如，2015-12-20 10:01:00.999999 需要8個字節，5個字節用于2015-12-20 10:01:00 ，3個字節用于.999999 而2015-12-20 10:01:00.9 只需要6個字節，1個字節用于小數秒精度。

　　TIMESTAMP 類型

　　TIMESTAMP 的顯示格式與 DATETIME 相同，顯示寬度固定在 19 個字符，日期格式為 YYYY-MM-DD HH：MM：SS，在存儲時需要 4 個字節。但是 TIMESTAMP 列的取值范圍小于 DATETIME 的取值范圍，為 '1970-01-01 00：00：01'UTC～'2038-01-19 03：14：07'UTC。在插入數據時，要保證在合法的取值范圍內。

　　提示：協調世界時（英：Coordinated Universal Time，法：Temps Universel Coordonné）又稱為世界統一時間、世界標準時間、國際協調時間。英文（CUT）和法文（TUC）的縮寫不同，作為妥協，簡稱 UTC。

　　TIMESTAMP 與 DATETIME 除了存儲字節和支持的范圍不同外，還有一個最大的區別是：

　　DATETIME 在存儲日期數據時，按實際輸入的格式存儲，即輸入什么就存儲什么，與時區無關；
　　而 TIMESTAMP 值的存儲是以 UTC（世界標準時間）格式保存的，存儲時對當前時區進行轉換，檢索時再轉換回當前時區。即查詢時，根據當前時區的不同，顯示的時間值是不同的。
　　提示：如果為一個 DATETIME 或 TIMESTAMP 對象分配一個 DATE 值，結果值的時間部分被設置為 '00：00：00'，因此 DATE 值未包含時間信息。如果為一個 DATE 對象分配一個 DATETIME 或 TIMESTAMP 值，結果值的時間部分被刪除，因此DATE 值未包含時間信息。

　　MySQL TIMESTAMP以UTC值存儲。但是，MySQL將DATETIME值存儲為沒有時區的值。我們來看下面的例子。

　　首先，將當前連接的時區設置為+00:00。

　　SET time_zone = '+00:00';
　　接下來，創建一個表命名timestamp_n_datetime是由兩列組成：ts與dt用TIMESTAMP和DATETIME使用下面的語句類型。

　　CREATE TABLE timestamp_n_datetime (
　　    id INT AUTO_INCREMENT PRIMARY KEY,
　　    ts TIMESTAMP,
　　    dt DATETIME
　　);
　　然后，將當前日期和時間插入 timestamp_n_datetime 表的兩個ts和每dt列，

　　INSERT INTO timestamp_n_datetime(ts,dt)
　　VALUES(NOW(),NOW());
　　之后，從timestamp_n_datetime表中查詢數據。

　　SELECT
　　    ts,
　　    dt
　　FROM
　　    timestamp_n_datetime;

　　DATETIME和TIMESTAMP列中的值都相同。

　　最后，將連接的時區設置為+03:00并再次從timestamp_n_datetime表中查詢數據。

　　SET time_zone = '+03:00';
　　
　　SELECT
　　    ts,
　　    dt
　　FROM
　　    timestamp_n_datetime;

　　如您所見，TIMESTAMP列中的值不同。這是因為當我們更改時區時，TIMESTAMP列以UTC格式存儲日期和時間值，根據新時區調整列的值。

　　這意味著如果您使用TIMESTAMP數據存儲日期和時間值，則在將數據庫移動到位于不同時區的服務器時應認真考慮。

　　關于“mysql日期類型是什么”這篇文章的內容就介紹到這里，感謝各位的閱讀！

（編輯：武林網）

上一篇：mysql如何把時分秒轉換成秒數

下一篇：mysql中clob和blob的差別有哪些