时间是一个系统设计中看似简单,但又很重要而且容易造成出错的地方。主机差评君把用python和mysql来做系统的时候遇到的一些常见问题梳理一下,防止自己忘记,也给需要的朋友一些参考。因为没有时间仔细钻研过源码,所有的结论均是经过参考文档和自己测试得出的。

mysql 的时间处理

mysql的时间类型主要有 datetime 和timestamp

粗略一看他们都能表示 YYYY-mm-dd HH:MM:SS 这种精度的时间值,但实际上机制很不一样。

参考mysql的官方文档

https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html

mysql文档中时间表达

在mysql5.6.4 之前, timestamp 只有 4个字节,换言之只能表达精确到秒的时间,datetime有8个字节,可以表示非常精确的时间。我不是很建议使用mysql 5.6.4 的mysql版本。

在mysql 5.6.4 之后, timestamp 用4 字节表示秒部分,用额外的存储表示不足一秒的部分,datetime也使用 5字节 + 分数秒部分的方式表示。 可以表示大精度的时间。通常表达 '1970-01-01 00:00:01.000000' t到 '2038-01-19 03:14:07.999999' 的精度已经可以满足大部分需求。

对于有国际化需求的系统,建议只使用 timestamp来存取时间字段。根据mysql的文档(https://dev.mysql.com/doc/refman/5.7/en/datetime.html),timestamp是这样存取的:

timestamp存储

用户打开数据库连接session时,有一个时区timezone设定,当用户存储一个时间 ‘2022-10-01 00:00:00’ 时 ,mysql会把这个时间转换成UTC时间(可以理解为0时区)存储起来。如果当前时区是+08:00 ,那么数据库中存储的timestamp 是 ‘2022-09-30 16:00:00’。

timestamp读取

从存储中读出timestamp字段的utc值,再吧utc时间转化为当前时区的时间,提供给客户端程序。

datetime存取

而datetime的的存储,仅仅是把字符串转化为时间类型,存储到数据库中去。本质上和string差不多。写进去的是什么,读出来的就是什么。

从上面的比较很容易推测到,如果使用timestamp存储时间,那么不管数据库的时区修改成何种时区,也不管数据库连接session的时区是何种时区,mysql都能提供准确的时间给客户端处理;如果使用的datetime,则修改数据库时区或者数据库连接session的时区,都可能产生混淆,导致出现各种不兼容和错误。

所以,我建议mysql使用者应该尽量使用 timestamp来存取时间值。这样可以避免绝大部分错误。

使用timestamp,当然也有一些不方便的地方:

1 连接数据库需要指定timezone(废话,本身也需要指定),写入速度理论上稍慢于 datetime(时区转化)

2 如果当前的时区设置是 ‘system’, 则在linux下需要频繁调用OS级别的时区定位、本地时间转换调用。

如果让linux参与了时间转换,可能会频繁调用到有一些有系统级锁的系统调用。如果你做的是一个高并发的系统,有倒霉地使用mysql来做存储引擎,可能被这个系统调用拖慢了整体的并发量造成性能问题。这个坑隐藏极深,可能会把小白绕进去后无法自拔。

建议不要使用 system 作为timezone设置值,需要明确指定 +0800 这种值,避免产生系统调用。

总结一下,很多朋友使用mysql处理时间的时候,默认选择 datetime,这在一般情况下问题是不大的,但是有朝一日你的系统需要处理一些其他时区的时间,那么将会非常尴尬,甚至导致一些灾难性后果。如果从头开始设计一个兼容时区的应用,其实应该使用 timestamp。

python(3) 的时间处理

上面对mysql 的timezone时区的讨论,其实对于python这种语言层面的时间处理,也是有类似的设计困境。

python的主要时间处理库在 datetime 包中,还有一个在time包中。

不少人诟病 python 的datetime 包特别难用别扭,这有几分道理。

其中,datetime包里面常用的有 datetime 、timezone和 timedelta 三个类

datetime类是有时区处理能力的,但datetime又不强制必须有时区,所以也有一些坑。

坑的演示:datetime或者 now 当前时间, utcnow 当前UTC时间

>>>datetime.now()
datetime.datetime(2022, 9, 18, 22, 48, 4, 634701)

>>>datetime.utcnow()
datetime.datetime(2022, 9, 18, 14, 48, 11, 500411)

从上面看到 now 和 utcnow 的时间文字表达式,确实是相差了8小时,看起来没有毛病。

”有坑“的地方,是这两个对象都没有写进时区对象。

datetime对象提供了一个astimezone方法,允许用户将一个时间转为另一个时区的时间表达。

如果作用于一个没有指定 timezone 的 datetime对象,可能有灾难性的后果。

>>d2 = datetime.utcnow()

>>d2
datetime.datetime(2022, 9, 18, 14, 56, 7, 314961)

>>d2.astimezone( timezone.utc)
datetime.datetime(2022, 9, 18, 6, 56, 7, 314961, tzinfo=datetime.timezone.utc)

一个我们认为已经是utc时间的 d2 ,在转为utc时区的timestamp,理论上是不变的,但又以当前+8时区为基准,向前倒扣了8个小时。

于是,可以得到一个结论,不带时区的datetime对象,使用时区转换是非常危险的。

d2
datetime.datetime(2022, 9, 18, 14, 58, 55, 23630)

一个utc的d2对象,强制设置timezone为 utc

d3 = d2.replace(tzinfo=timezone.utc)

datetime.datetime(2022, 9, 18, 14, 58, 55, 23630, tzinfo=datetime.timezone.utc)

看到多了时区信息

再使用astimezone转时区

d3.astimezone( timezone.utc )

datetime.datetime(2022, 9, 18, 14, 58, 55, 23630, tzinfo=datetime.timezone.utc)

d3.astimezone( timezone(timedelta(hours=8)) )

datetime.datetime(2022, 9, 18, 22, 58, 55, 23630, tzinfo=datetime.timezone(datetime.timedelta(seconds=28800)))

可以看到,已经可以非常愉快地转换各种时区了。

python + mysql结合起来处理时间字段的实践

讲一下python操纵mysql数据库的一些实践

1 数据库设计时间只使用timestamp类型

2 python通过dsn连接数据库,明确指定时区,这个时区是程序当前的时区

3 python写入数据库时,时间字段可能需要转成数据库时区的对应值。

第三点需要举例说明一下:

数据库mysql是 utc时区的,数据库连接使用了 +8 时区,程序拿到了一个 +2 时区表达的时间,那么假设这个 +2 时区的时间是 2022-10-01 10:00:00 ,则需要把 2022-10-01 10:00:00 转为 2022-10-01 16:00:00 再写入数据库。 这样才能写入正确的timestamp时间。

如果在时间转换这里担心出错,那么可以在python里面把所有时间都转为 utc 时间的 timestamp数字表达方式,如 1663415127.695499 ,通过 FROM_UNIXTIME( ) 的mysql函数,准确把这个秒表示法的的文字表达方式转成准确数据表达。 例如, FROM_UNIXTIME(1663415127.695499) 的结果是多少,完全取决于当前时区是多少。 虽然这样做会导致一点点性能损失,但也不失为一个稳妥的办法。

啰嗦了一些细节,没有很深的见解,只是个人的一些实践。关于python mysql的小坑会不定期同步在 https://zhuji188.com/655.html 中,欢迎查阅。

猜你喜欢
发表评论

电子邮件地址不会被公开。 必填项已用*标注

评论信息