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

在 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>

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

paragraph

datetime

时区

MySQL

6 条回复 • 2022-09-19 13:35:11 +08:00