mysql日期用什麼型別

2022-06-15 18:00:49

有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;

1.png

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;

2.png

之後,使用兩位數年份格式將資料插入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;

3.png

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值需要更多儲存空間,如下表所示:

分數秒精度儲存 (位元組)
00
1, 21
3, 42
5, 63

例如,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;

4.png

DATETIME和TIMESTAMP列中的值都相同。

最後,將連線的時區設定為+03:00並再次從timestamp_n_datetime表中查詢資料。

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

5.png

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

這意味著如果您使用TIMESTAMP資料儲存日期和時間值,則在將資料庫移動到位於不同時區的伺服器時應認真考慮。

【相關推薦:】

以上就是mysql日期用什麼型別的詳細內容,更多請關注TW511.COM其它相關文章!