MySQL中的Datetime与Timestamp比较

  • A+
所属分类:数据分析

Datetime与Timestamp使用细节

首先 DATETIM和TIMESTAMP类型所占的存储空间不同,前者8个字节,后者4个字节,这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59,后者范围为1970-01-01 08:00:01到2038-01-19 11:14:07。所以可以看到TIMESTAMP支持的范围比DATATIME要小,容易出现超出的情况.

其次,TIMESTAMP类型在默认情况下,insert、update 数据时,TIMESTAMP列会自动以当前时间(CURRENT_TIMESTAMP)填充/更新。

第三,TIMESTAMP比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响

所以一般来说,我比较倾向选择DATETIME,至于你说到索引的问题,选择DATETIME作为索引,如果碰到大量数据查询慢的情况,也可以分区表解决。

----------------------------------

其实速度上差别不是很大,你可以自己做做测试就知道了。内部存储都是整数,只不过datetime和timestamp会仅仅在显示的时候,显示为人能读的日期(当然存储空间有点区别,整形和timestamp都是4字节,datetime是8字节),做索引也应该没什么区别,这个不敢确定,如果有请指正我……

另外datetime和timestamp相对于int来说也有一个小小的好处,就是对于时间类型来说,可以有一系列的时间函数可以用

这几个类型的选择还是看你的需求。

我用timestamp比较多,对于记录日志什么的需求,timestamp绝对够用了,除非你保证说你的程序能一直用到2038年,就算如此也可以用迁移程序处理……

如果需求是允许用户保存一些超过timestamp能保存的时间(@QingchaoWu 已经给出了timestamp的范围)比如说todo list什么的,允许用户计划38年以后的事情,那就用datetime好了。

------------------------------------

两者都是时间类型字段,格式都一致。两者主要有以下四点区别:

最主要的区别-受时区影响不同。timestamp会跟随设置的时区变化而变化,而datetime保存的是绝对值不会变化。

详细可以阅读这篇博客的演示:http://www.tech-recipes.com/r...
一个timestamp字段,一个datetime字段,修改时区SET TIME_ZONE = "america/new_york";后,timestamp字段的值变了!
因此,如果应用场景有跨时区要求的要特别注意这点。

占用存储空间不同。timestamp储存占用4个字节,datetime储存占用8个字节:http://dev.mysql.com/doc/refm...

可表示的时间范围不同。timestamp可表示范围:1970-01-01 00:00:00~2038-01-09 03:14:07,datetime支持的范围更宽1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

索引速度不同。timestamp更轻量,索引相对datetime更快。

----------------------------------------------------------
MySQL DATETIME类型和Timestamp之间的转换
DATETIME -> Timestamp: UNIX_TIMESTAMP(...)

Timestamp -> DATETIME: FROM_UNIXTIME(...)

MySQL> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()));
+--------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())) |
+--------------------------------------+
| 2010-09-06 03:27:41 |
+--------------------------------------+

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: