类型 | 所占字节 | 格式 | 范围 |
TIMESTAMP | 4字节 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01utc到2038-01-19 03:14:07utc |
DATETIME | 5字节 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00到9999-12-31 23:59:59 |
DATE | 3字节 | YYYY-MM-DD | 1000-01-01到9999-12-31 |
TIME | 3字节 | HH:MM:SS | -838:59:59到838:59:59 |
YEAR | 1字节 | YYYY | 1901到2155 |
注:在5.6.4之前,datetime存储占用8个字节,而timestamp是占用4字节;但是在5.6.4之后,由于这两个类型允许有小数部分,所以占用的存储空间和以前不同;MySQL规范规定,datetime的非小数部分需要5个字节,而不是8个字节,而timestamp的非小数部分是需要4个字节,并且这两个部分的小数部分都需要0到3个字节,具体取决于存储值的小数秒精度
1. TIMESTAMP是以utc格式存储,会自动检索当前时区对时间进行转换,而DATETIME不会。
2. 存入null时,TIMESTAMP会自动存储当前时间,而DATETIME存储null值。
3. 时间计算:
DATETIME翻译为汉语即"时间戳",它是当前时间到 Unix元年(1970 年 1 月 1 日 0 时 0 分 0 秒)的秒数。对于某些时间的计算,如果是以 DATETIME
的形式会比较困难,假如我是 1994-1-20 06:06:06
出生,现在的时间是 2016-10-1 20:04:50
,那么要计算我活了多少秒钟, DATETIME还需要函数进行转换,但是 TIMESTAMP 直接相减就行。
1.需要跨时区计算时间用 或者 需要自动更新时间的TIMESTAMP
计算一架从北京飞往纽约的飞机的飞行时间。这个场景中,如果使用 TIMESTAMP 来存时间,起飞和降落时间的值,都会被转换成 UTC 时间,所以它们直接相减即可获得结果。但如果使用 DATATIME 格式存时间,还需要进行转换,才可以完成,容易出错。
2.记录创建修改时间 或者 时间范围大于2038 用DATETIME
DATATIME作为记录时间,现在都已经2022年了,很快就到2038年啦,使用DATATIME不需要担心超过范围。
当然在两者都满足使用的情况下,所占字节越小越好,TIMESTAMP比DATATIME好。
还有一种情况,即不用TIMESTAMP也不用DATATIME,而是用BIGINT。存储自纪元以来的毫秒数(如果使用的是 Java,则用 System.currentTimeMillis() 获取当前时间)
这样有几个优点:
1. 可以在迁移数据库时避免因为数据类型差异。比如MySQL的DATETIME类型和Oracle的DATETIME类型之间可能存在差异,timestamp类型的精度可能也存在差异,MySQL的timestamp精度不是一开始就支持毫秒精度的。
2. 没有时区问题。无论是哪个时区,因为开始计算的时间不同,无论当前时间如何,跨度是一致的。也没有timestamp和datatime的范围问题。是对timestamp的补充。
3. InnoDB存储引擎下,通过时间范围查找,性能bigint > datetime > timestamp,通过时间排序,性能bigint > timestamp > datetime。综合来讲,bigint性能最好。
以上就总结完了,大家可根据需要选择。下次见!