有5種型別:1、YEAR,用於表示年,格式為「YYYY」;2、TIME,用於只需要時間資訊的值,格式為「HH:MM:SS」;3、DATE,用於僅需要日期值時,格式為「YYYY-MM-DD」;4、DATETIME,用於需要同時包含日期和時間資訊的值,格式「YYYY-MM-DD HH:MM:SS」;5、TIMESTAMP,與DATETIME類似,用於需要同時包含日期和時間資訊的值。
本教學操作環境:windows7系統、mysql8版本、Dell G3電腦。
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;
之後,使用兩位數年份格式將資料插入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日期用什麼型別的詳細內容,更多請關注TW511.COM其它相關文章!