[Python] 老生常谈:简单缺容易掉坑,分享 mysql 和 Python 处理时间的一些经验

时间是一个系统设计中看似简单,但又很重要而且容易造成出错的地方。主机差评君把用 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

<figure class=”wp-block-image size-full”><figcaption>mysql 文档中时间表达</figcaption></figure>

在 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 级别的时区定位、本地时间转换调用。

<figure class=”wp-block-image size-full”></figure>

如果让 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 中,欢迎查阅。

发表回复

您的电子邮箱地址不会被公开。