今天组内展开了一个关于时间在mysql里应该用什么格式存储的问题,datetime,timestamp,int64,目前有这么三种选项. 抛开使用场景来谈选择是无意义的,先了解下这三种类型有什么区别.

timestamp

1. 4个字节,所占的存储空间小,索引更快
2. 可表示的时间范围:’1970-01-01 00:00:01′ UTC to ’2038-01-09 03:14:07′ UTC
3. 存储的时候从当前的时区自动转成utc时间存储,读取的时候再从utc转成当前所在的时区时间

datetime

1. 8个字节
2. 可表示的时间范围:’1000-01-01 00:00:00′ to ’9999-12-31 23:59:59′.
3. 存储的时候存储当前时区的时间,不做转换;同样的,读取的时候也是直接将存储的时间原样读取出来

int64

1. utc时间戳,可以精确到毫秒,并且无时间范围的限制.
2. 可读性不好

以上特性区别讲完之后,再来讲下常见的使用场景

1. 支付交易场景

用户在东八区(0时区)2019年12月1日0点买了一件东西,然后用户到了中国(东八区),页面展示用户的购买信息的时候,页面应该能根据用户所在时区调整展示时间.

timestamp:存储没问题,读取没问题

datetime:存储必须要存储成utc时间,也就要求客户端必须强制设置时区.读取的时候也需要客户端根据用户所在时区进行转换

int64:存储时,客户端需要使用utc时间转换,读取时根据用户所在时区转成对应的时间格式

1. 需要存储较大范围的时间

timestamp:存在局限性

datetime:无

int64:无

1. 日常case排查

以上两点都是线上场景,但是线下的运营也很重要,每个开发基本都要花1/3的时间在各种case排查上,所以时间的可读性和准确性也是很重要的考量因素

timestamp:需要设置client的时区;可读性好

datetime:client端读取的是utc时间(写入的时候都以utc时间写入),根据排查case的不同,和timestamp相比,有利有弊;可读性好

int64:毫无可读性

以上是我想到的可能涉及的几点考虑因素,由于2和3都存在压倒性的对比优势,最终还是考虑datetime.不过datetime使用的时候需要客户端保证写入utc时间,这点尤其重要.