参考文章:
MySQL进阶之路:日期类型datetime和timestamp区别在哪里?
重点总结:
二、不同点
接下来来说下他们的不同点。
1)存储范围不同:
datetime的存储范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999,而timestamp的范围是 1970-01-01 00:00:01.000000到 2038-01-19 03:14:07.999999(准备的来讲应该是UTC范围);
如果我们存储timestamp的时候,存了不在它范围内的时间值时,会直接抛出异常。
补充:
该DATE类型用于具有日期部分但没有时间部分的值。MySQL检索并DATE以 格式显示 值 。支持的范围是 到。
‘YYYY-MM-DD’‘1000-01-01’‘9999-12-31’该DATETIME类型用于包含日期和时间部分的值。MySQL检索并DATETIME以格式显示 值 。支持的范围是 到。
‘YYYY-MM-DD hh:mm:ss’‘1000-01-01 00:00:00’‘9999-12-31 23:59:59’该TIMESTAMP数据类型被用于同时包含日期和时间部分的值。 UTCTIMESTAMP的范围是UTC。 ‘1970-01-01
00:00:01’‘2038-01-19 03:14:07’
2)时区相关:
datetime存储与时区无关(准备来说是datetime只支持一个时区,就是存储时当前服务器的时区),而timestamp存储的是与时区有关。MySQL在存储TIMESTAMP时,会先将时间从当前服务器的时区转换为UTC(世界协调时)以进行存储,然后查询时从UTC转换为当前时区以进行返回。也就是说使用timestamp进行存储的时间返回的时候会随着数据库的时区而发生改变。而datetime的存储则与时区无关,数据是什么就存储什么,也就返回什么。
3)存储大小:
在5.6.4之前,datetime存储占用8个字节,而timestamp是占用4字节;但是在5.6.4之后,由于这两个类型允许有小数部分,所以占用的存储空间和以前不同;MySQL规范规定,datetime的非小数部分需要5个字节,而不是8个字节,而timestamp的非小数部分是需要4个字节,并且这两个部分的小数部分都需要0到3个字节,具体取决于存储值的小数秒精度。
另外,本文的相关内容可以参考MySQL官网链接:
https://dev.mysql.com/doc/refman/5.7/en/datetime.html
https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html
补充官网信息:
The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in ‘YYYY-MM-DD’ format. The supported range is ‘1000-01-01’ to ‘9999-12-31’.
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD hh:mm:ss’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.
实际操作,mysql8下:
date的范围可以从0000开始。以下是截图:
补充TIMESTAMP列类型
TIMESTAMP值可以从1970的某时的开始一直到2037年,精度为一秒,其值作为数字显示。
TIMESTAMP值显示尺寸的格式如下表所示:
:
±--------------±---------------+
| 列类型 | 显示格式 |
| TIMESTAMP(14) | YYYYMMDDHHMMSS |
| TIMESTAMP(12) | YYMMDDHHMMSS |
| TIMESTAMP(10) | YYMMDDHHMM |
| TIMESTAMP(8) | YYYYMMDD |
| TIMESTAMP(6) | YYMMDD |
| TIMESTAMP(4) | YYMM |
| TIMESTAMP(2) | YY |
±--------------±---------------+
“完整”TIMESTAMP格式是14位,但TIMESTAMP列也可以用更短的显示尺寸
创造最常见的显示尺寸是6、8、12、和14。
你可以在创建表时指定一个任意的显示尺寸,但是定义列长为0或比14大均会被强制定义为列长14。