MySQL日期和時間類型
MySQL有5種表示時間值的日期和時間類型,分别為、DATE,TIME,YEAR,DATETIME,TIMESTAMP。
TIMESTAMP類型有專有的自動更新特性,
TIMESTAMP類型有專有的自動更新特性,
TIMESTAMP類型有專有的自動更新特性,
日期類型
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiAjM2EzLcd3LcJzLcJzdllmVldWYtl2Pn5GcuAjN4UGNhBjM3EGZmBzMmNTO4QGOmNWMiBDO3YzMxUzMvwFO5gjM5QDOtUGall3LcVmdhNXLwRHdo9CXt92YucWbpRWdvx2Yx5yazF2Lc9CX6MHc0RHaiojIsJye.png)
類型 | 大小(位元組) | 範圍 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 時間值或持續時間 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和時間值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2037 年某時 | YYYYMMDD HHMMSS | 混合日期和時間值,時間戳 |
如果要用來表示年月日,通常用DATE 來表示。
如果要用來表示年月日時分秒,通常用DATETIME 表示。
如果隻用來表示時分秒,通常用TIME 來表示。
TIMESTAMP表示格式 :YYYY-MM-DD HH:MM:SS。
如果隻是表示年份,可以用YEAR 來表示。DATE,DATETIME,TIME是常用三種。
在 MySQL 中建立表時,對照上面的表格,很容易就能選擇到合适自己的資料類型。不過到底是選擇 datetime 還是 timestamp,可能會有點犯難。這兩個日期時間類型各有優點:datetime 的日期範圍比較大;timestamp 所占存儲空間比較小,隻是 datetime 的一半。
另外,timestamp 類型的列還有個特性:預設情況下,在 insert, update 資料時,timestamp 列會自動以目前時間(CURRENT_TIMESTAMP)填充/更新。“自動”的意思就是,你不去管它,MySQL 會替你去處理。
建表的代碼為:
create table t8 (
`id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
`id2` datetime default NULL
);
一般情況下,我傾向于使用 datetime 日期類型。
兩者之間的比較:
1. timestamp容易所支援的範圍比timedate要小。 并且容易出現超出的情況
2.timestamp比較受時區timezone的影響以及MYSQL版本和伺服器的SQL MODE的影響.
MySQL 時間類型:時間格式、所占存儲空間、時間範圍。
時間類型 存儲空間 時間格式 時間範圍
------------ --------- --------------------- -----------------------------------------
time 3 bytes HH:MM:SS -838:59:59 ~ 838:59:59
time 時間範圍居然有這麼大的範圍,特别是 time 可以取負值,有點奇怪。後來,看了 MySQL 手冊才知道這是為了滿足兩個日期時間相減才這樣設計的。
select timediff('2000:01:31 23:59:59', '2000:01:01 00:00:00'); -- 743:59:59
select timediff('2000:01:01 00:00:00', '2000:01:31 23:59:59'); -- -743:59:59
select timediff('23:59:59', '12:00:00'); -- 11:59:59
注意,timediff 的兩個參數隻能是 datetime/timestamp, time 類型的,并且這兩個參數類型要相同。即:datetime/timestamp 和 datetime/timestamp 比較;time 和 time 相比較。
雖然 MySQL 中的日期時間類型比較豐富,但遺憾的是,目前(2008-08-08)這些日期時間類型隻能支援到秒級别,不支援毫秒、微秒。也沒有産生毫秒的函數。
CREATE TABLE t(d DATE,t TIME,dt DATETIME)
-- 插入目前日期
INSERT INTO t VALUES(NOW(),NOW(),NOW());
-- 檢視
SELECT * FROM t;
TIMESTAMP 也用來表示日期,和時區相關(DATETIME則隻能反應出插入時當地的時區,其他時區的人檢視資料必然會有誤差的。)
CREATE TABLE t3(d1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
INSERT INTO t3 VALUES(NOW());
最後采用不同的格式将日期“2007-9-3 12:10:10”插入到DATETIME列中
CREATE TABLE t6(dt DATETIME);
INSERT INTO t6 VALUES('2007-9-3 12:10:10');
INSERT INTO t6 VALUES('2007/9/3 12+10+10');
INSERT INTO t6 VALUES('20070903121010');
INSERT INTO t6 VALUES(20070903121010);
SELECT * FROM t6;