VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
DATETIME 与 TIMESTAMP
这两种类型看起来相似,但在关键时刻(读取时)的行为不同。TIMESTAMP 会自动转换值:
- INSERT/UPDATE 时:从会话
time_zone转换为 UTC 存储 - SELECT 时:从 UTC 转换回会话
time_zone显示
DATETIME 不做任何转换。您写入什么值,就取回什么值。
像 'America/New_York' 这样的命名时区需要加载时区表(请参阅下文的 命名时区),刚初始化的服务器没有这些表。在运行下面的示例之前,请用 SELECT COUNT(*) FROM mysql.time_zone_name; 检查;如果结果是 0,请先加载时区表,或改用 '-05:00' 这样的数字偏移量。
2038 年问题
TIMESTAMP 以 32 位 Unix 时间戳存储。它无法表示 2038-01-19 03:14:07 UTC 之后的日期。任何存储未来日期的应用程序(预订、订阅、计划事件)都应使用 DATETIME 以避免触及这个限制。
服务器时区与会话时区
MySQL 有两个相互独立的时区设置:time_zone 显示为 SYSTEM,MySQL 使用的是操作系统的时区。这在操作系统发生变化(服务器迁移、夏令时切换)之前都能正常工作,之后您的时间戳就会发生偏移。
最佳实践:把服务器显式设置为 UTC,并在应用程序中处理显示转换。
命名时区
'+00:00' 和 'UTC' 都有效,但像 'America/New_York' 这样的命名时区需要加载时区表。如果 CONVERT_TZ() 返回 NULL 或命名时区失败,说明这些表缺失。
AUTO_UPDATE 列
TIMESTAMP 列支持在插入和更新时自动填充:
CURRENT_TIMESTAMP 就以 UTC 求值,因此无论谁从哪个时区连接,created_at 和 updated_at 的存储都是一致的。
您可以用 DATETIME 和 NOW() 做同样的事:
NOW() 返回会话时区中的当前日期和时间,这是把会话统一为 UTC 的另一个理由。
常见问题
我应该把时间戳以 UTC 存入 DATETIME,还是使用 TIMESTAMP?
如果您的日期不超过 2038 年的上限,TIMESTAMP 更简洁,它会自动处理 UTC 转换。对于 2038 年之后的未来日期,或需要存储 1970 年之前的历史日期的应用程序,请使用 DATETIME,并在应用层强制使用 UTC,也就是在插入前始终转换为 UTC。
为什么服务器迁移后时间戳会偏移?
旧服务器使用time_zone = SYSTEM 指向某个操作系统时区;新服务器指向另一个时区(或 UTC)。以 UTC 存储的 TIMESTAMP 值在两个时区中都能正确显示,但如果有任何值是按系统时区而不是 UTC 存储的,它们就会偏移。迁移前请在两台服务器上用 SELECT @@global.time_zone 进行审计。
如何只存储日期而不带时间部分?
使用DATE。它占 3 字节存储,没有一天中的时间,也没有时区歧义。无论会话时区如何,像 WHERE date_col = '2024-03-15' 这样的操作都是无歧义的。
故障排除
另请参阅
- MySQL 日期和时间函数:用于处理 DATETIME 和 TIMESTAMP 值的函数