一、MySQL中 日期和時間類型

參考?MySQL 數據類型（還有其他知識）

參考?SQL 數據類型

表示時間值的日期和時間類型為 DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個時間類型有一個有效值范圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。

TIMESTAMP類型有專有的自動更新特性。

MySQL中 日期和時間類型類型大小(字節)范圍格式用途

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/2038 結束時間是第?2147483647?秒，北京時間?2038-1-19 11:14:07，格林尼治時間 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和時間值，時間戳

---

二、日期和時間類型的使用

開始使用的是 Mysql 5.5.47

創建表時出錯了，遇到兩個問題。。

問題一：

[Err] 1067 - Invalid default value for 'create_time'

查閱資料，原來是版本問題

在5.5到5.6.4版本里，對于DEFAULT CURRENT_TIMESTAMP 子句，只能TIMESTAMP類型列上指定。

而從5.6.5開始以后的版本，對于DEFAULT CURRENT_TIMESTAMP子句可以指定到TIMESTAMP或者DATETIME類型列上。

問題二：

想給一個類型為date字段設置一個default value（CURRENT_DATE，CURRENT_DATE()，NOW()）但試了很久都不行。最后試了下 DEFAULT NULL 成功了，再試下 DEFAULT 1也成功了。最后網上查了一下資料，原因是date類型的default value 一定要是一個常量，不能是一個函數或者是表達式。所以不能給MySQL的date類型的列設置默認值。

---

三、TIMESTAMP和DATETIME比較

相同點：

兩者都可用來表示YYYY-MM-DD HH:MM:SS 類型的日期。

不同點：

他們的的存儲方式，大小(字節)，表示的范圍不同。

TIMESTAMP，它把客戶端插入的時間從當前時區轉化為UTC（世界標準時間）進行存儲。查詢時，將其又轉化為客戶端當前時區進行返回。

DATETIME，不做任何改變，基本上是原樣輸入和輸出。

總結：TIMESTAMP和DATETIME?都可用來表示YYYY-MM-DD HH:MM:SS 類型的日期， 除了存儲方式和存儲范圍以及大小不一樣，沒有太大區別。但對于跨時區的業務，TIMESTAMP更為合適。

---

四、時間與時間戳之間轉換

有些應用生成的時間戳是比這個多出三位，是毫秒表示，如果要轉換，需要先將最后三位去掉（標準的10位數字，如果是13位的話可以以除以1000的方式），否則返回NULL

#將時間轉換為時間戳unix_timestamp SELECT UNIX_TIMESTAMP('2019-02-22 13:25:07'); #1550813107#將時間戳轉換為時間from_unixtime SELECT FROM_UNIXTIME(1550813107); #2019-02-22 13:25:07#NOW SELECT UNIX_TIMESTAMP(NOW()); #1550813420 SELECT FROM_UNIXTIME(1550813420); #2019-02-22 13:30:20

參考?MySQL的10位或13位時間戳獲取，表示及13位時間戳的存儲

---

五、按時間日期查詢

然后我將 Mysql 改為 Mysql 5.7.17，升一下版本

參考?MySQL DATE_FORMAT() 函數

#MySQL 按時間日期查詢 DROP TABLE IF EXISTS tb_test; CREATE TABLE IF NOT EXISTS tb_test(id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',name VARCHAR(100) DEFAULT NULL COMMENT '名稱',create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COMMENT '時間測試表';#插入數據 INSERT INTO tb_test (name) VALUES ('luo'), ('lei'), ('luolei'), ('xing'), ('dxx');#修改數據 UPDATE tb_test SET name='luo1', update_time='2021-01-23' WHERE id=100; UPDATE tb_test SET name='luo2', update_time='2022-02-23' WHERE id=101; UPDATE tb_test SET name='luo3', update_time='2023-03-23' WHERE id=102;#根據年月日查數據 SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y-%m-%d')='2019-02-22'; #根據年月查數據 SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y-%m')='2021-01'; #根據年查數據 SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y')='2022';#根據日期區間查詢數據,并排序 SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y') BETWEEN '2019' AND '2023' ORDER BY update_time ASC;

個人網站歡迎來訪

http://www.leixingke.com/

總結

以上是生活随笔為你收集整理的MySQL中时间日期类型的使用的全部內容，希望文章能夠幫你解決所遇到的問題。

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