当前位置：首页 > 问答 > 正文

数据库里时间格式那些事儿，怎么转换才不出错分享给你

根据多位开发者在社区论坛如CSDN、V2EX、Stack Overflow上的常见问题讨论，以及像“程序员小灰”、“阿里技术”等公众号的历史文章观点综合整理）

数据库里时间格式那些事儿,怎么转换才不出错分享给你

咱们做开发的时候,肯定都踩过时间的坑，尤其是刚入门那会儿，觉得时间嘛，不就是个字符串，存进去读出来就行了，结果呢？不是少了一天，就是时间对不上，或者一换时区就全乱套了，今天咱就聊聊这事儿，目标就一个：怎么处理时间，才能最大可能地不出错。

第一件事：认清数据库的“真面目”

首先你得知道,数据库里存时间，可不是像我们想的那样存成“2023-10-26 15:30:00”这么个简单的字符串，它内部有自己专门的类型来管理，常见的比如：

DATETIME：这个你可以理解成就是一个“日历和钟表”，它记录的就是一个具体的时间点，2023年10月26日下午3点半”，它很直观，但有个特点：它不附带时区信息，你存进去的是什么时间，读出来就是什么时间，如果你的用户遍布全球，用这个就要非常小心了。
TIMESTAMP：这个家伙更聪明一点，它通常记录的是从“1970年1月1日零点”（也叫时间戳纪元）到现在经过的秒数，最关键的是，它通常是和时区关联的，数据库在存的时候，可能会把它转换成UTC时间（世界标准时间）存起来；在取的时候，再根据数据库连接设置的时区，转换回当地时间显示，这对于跨时区的应用是福音，但也容易因为时区设置不对而踩坑。
DATE 和 TIME：这两个就简单了，一个只存日期（年-月-日），一个只存时间（时-分-秒），用在不需要完整时间戳的场景。

（来源观点参考：“程序员小灰”公众号在《时间处理，一不小心就踩坑》一文中强调，理解DATETIME和TIMESTAMP的本质区别是避免时区问题的第一步）

第二件事：和程序打交道的最佳姿势

你的应用程序（比如Java、Python、PHP）怎么和数据库传递时间呢？这里有个黄金法则：

永远不要用字符串拼接的方式来处理时间！

你可千万别写出这样的SQL："INSERT INTO table VALUES ('2023-10-26 15:30:00')"，这样做死得快，因为一旦格式有细微差别，或者数据库期待的格式和你写的不一样，立马报错。

那应该怎么做？

使用“参数化查询”或“预处理语句”，并且传递真正的时间对象。

举个例子：

在你的SQL语句里,不要直接写时间值，而是用一个占位符（比如问号或者 @time），通过程序代码，把这个时间对象作为参数传递给数据库驱动，数据库驱动会帮你处理好所有格式转换的问题，你根本不用操心该写成什么格式的字符串，这不仅是防出错的最佳实践，也是防止“SQL注入”攻击的安全底线。

（来源观点参考：Stack Overflow上关于时间处理的无数高赞回答都一致推荐使用预处理语句和原生时间类型对象，而非字符串拼接）

第三件事：时区——那个最磨人的“小妖精”

时间问题里,80%的坑都是时区挖的，记住几个原则：

统一存储时区：推荐用UTC。 最好的习惯是，在存入数据库之前，就把所有时间都转换成UTC时间，这样，数据库里就是一个统一的标准，没有歧义，一个北京的用户下午3点发了帖子，存的时候可以转换成UTC时间的上午7点存进去。
转换在应用层完成： 当需要显示时间给用户看的时候，再根据用户所在的时区，把UTC时间转换回当地的正确时间，现在很多前端框架（比如JavaScript的库）都能很方便地做这个事。
检查你的数据库连接： 即使你存的是UTC，查出来却不对，很可能是你的数据库连接池的时区设置有问题，确保你的应用服务器、数据库服务器以及数据库连接的时区设置都是一致的，或者你清楚地知道它们之间的差异并能正确处理。

（来源观点参考：阿里技术公众号在《全球业务下的时间戳设计》一文中详细阐述了采用UTC存储、本地化展示的架构优势）

第四件事：一些零碎但实用的提醒

时间戳”： 有时候我们也会在数据库里存一个整数，也就是从1970年1月1日开始的毫秒数或秒数，这个做法的好处是绝对精确，没有时区歧义（因为它本身就是UTC时间），计算时间间隔特别方便，缺点是极度不人性化，人眼根本看不懂，所以要根据业务场景选择。
默认值”： 经常需要记录数据的创建时间，你可以直接在数据库表结构里设置字段的默认值，比如MySQL的CURRENT_TIMESTAMP，这样即使程序不传这个值，数据库也会自动帮你填上当前时间，非常可靠。
时间范围”： 不同的时间类型有各自的表示范围，比如MySQL的TIMESTAMP只能用到2038年（这就是著名的2038年问题），而DATETIME的范围就大得多，如果你的应用要管很久远的时间（比如历史纪年或者未来规划），要选对类型。

总结一下核心心法：