在数据库中存储时间戳:DATETIME vs INT vs BIGINT

存储时间戳的三种方式

大多数数据库至少提供三种选择:原生 datetime 类型(TIMESTAMP、DATETIME、TIMESTAMPTZ)、普通整数(INT 或 BIGINT),或字符串(VARCHAR)。它们在存储大小、查询易用性、时区处理和面向未来方面各有取舍。对大多数产品数据库,原生 datetime 列是最佳默认,因为数据库可以把该值当作时间(而非匿名数字)来比较、索引、截断、分组和格式化。

• 原生 datetime 类型 —— 最适合日期算术、时区转换和可读性
• BIGINT 整数 —— 适合高吞吐插入和简单的数值范围查询
• VARCHAR 字符串 —— 几乎总是错的:日期的字符串比较只对严格的 ISO 8601 格式有效
• INT 整数 —— 除非已彻底核查 2038 年边界,否则对未来时间戳应避免

MySQL:TIMESTAMP vs DATETIME vs INT

MySQL 有两种看起来相似但行为差异很大的日期时间类型——其中一种有硬性的到期日。当你想要 UTC 与会话时区之间自动转换时,TIMESTAMP 很方便,但它历史上的 32 位范围使其对面向未来的产品数据有风险。DATETIME 按你提供的字面日期时间存储,当应用在写入前统一使用 UTC 时通常更清晰。

• TIMESTAMP:内部以 32 位 Unix 秒存储 —— 限于 1970-01-01 至 2038-01-19
• TIMESTAMP:在插入/读取时自动在 UTC 与会话时区之间转换
• DATETIME:存储字面日期时间,无时区。范围 1000-01-01 至 9999-12-31。不受 Y2038 影响。
• DATETIME:不转换时区 —— 你在应用层控制 UTC
• 建议:新表使用带显式 UTC 值的 DATETIME,以避开 2038 年限制

PostgreSQL:TIMESTAMPTZ 是正确选择

PostgreSQL 的 TIMESTAMP WITH TIME ZONE(TIMESTAMPTZ)在内部以 UTC 微秒存储时间戳,并在输出时转换为会话时区。对大多数用例而言,它是最安全、最正确的选择,因为它表示时间中的真实瞬间。这个名字可能误导:TIMESTAMPTZ 并不存储像 America/New_York 这样的原始时区标签。它存储瞬间,然后按当前会话时区显示。

• TIMESTAMPTZ:存储 UTC,在输出时转换为会话时区 —— 可移植且对夏令时安全
• TIMESTAMP(无时区):按字面值存储且不转换 —— 仅用于无时区数据
• EXTRACT(EPOCH FROM col):从任意 TIMESTAMP 列以 float 返回 Unix 秒
• TO_TIMESTAMP(epoch):把 Unix 秒转换回 TIMESTAMPTZ

索引与查询性能

对普通应用表,原生 datetime 列与 BIGINT epoch 列之间的性能差异很少是决定因素。查询形态、索引设计、分区和行数更重要。先选择能正确保留含义的类型,再为应用实际运行的范围查询为其建立索引。

• 三种类型都支持 B-tree 索引和高效的范围查询
• 在超大表的等值和范围扫描上,BIGINT 整数略快
• 原生 datetime 类型允许按日期部分的索引查询:WHERE created_at::date = '2024-01-01'
• VARCHAR 时间戳的性能最差 —— 字符串比较不识别日期

何时适合用 BIGINT 存储 epoch

当数据呈事件型、以追加为主、且已由另一个系统产生为 Unix 时间时,BIGINT 是合理的。分析管道、遥测流、队列和紧凑的二进制协议常用 epoch 毫秒,因为数值比较快且与语言无关。代价是可读性:人需要转换器,而 SQL 的日期算术会更冗长。

• 如果 JavaScript 客户端直接产生事件,Unix 毫秒使用 BIGINT
• 如果源系统是 Unix 风格且秒精度足够,Unix 秒使用 BIGINT
• 在列名中记录单位:created_at_ms 比 created_at_epoch 更清晰
• 如果分析师需要可读的 SQL 查询,添加一个生成的 datetime 列
• 因 32 位范围限制,面向未来的现代时间戳应避免使用 INT

推荐的模式设计

对大多数 Web 应用,把瞬间以 UTC 存储,只有在需要重建本地挂钟意图时才单独存储用户偏好的时区。安排在 America/New_York 上午 9:00 的会议,不同于在精确 UTC 瞬间创建的事件日志;请对这些情况分别建模。

• 事件日志:PostgreSQL 用 created_at TIMESTAMPTZ,MySQL 用 UTC 的 created_at DATETIME
• JavaScript 事件采集:created_at_ms BIGINT 加上清晰的 API 文档
• 重复的本地排程:存储 local_date、local_time 和 timezone_id,再计算下一个瞬间
• 过期时间戳:expires_at 用原生 datetime,或 expires_at_seconds 用显式 Unix 秒
• 审计表:把 created_at 和 updated_at 都保留为原生 datetime 列,以便可读地调试

数据库时间戳常见问题

数据库中应存 UTC 还是本地时间?

为事件时间戳存储 UTC,显示时转换为本地时间。当用户的本地挂钟意图重要时(如重复会议或营业时间),单独存储一个时区标识符。

BIGINT 比 TIMESTAMP 更好吗?

通常不是。BIGINT 对数值型 epoch 管道有用,但原生 datetime 类型在 SQL 日期算术、可读调试和时区感知输出方面更容易。

MySQL 应使用 TIMESTAMP 还是 DATETIME?

对新的应用表,带 UTC 值的 DATETIME 通常更安全,因为它避开了 2038 年范围限制,且不会悄悄依赖会话时区转换。

时间戳应存为 UTC 还是带时区?

把瞬间以 UTC 存储(PostgreSQL 用 TIMESTAMPTZ,MySQL 用带 UTC 值的 DATETIME),显示时转换为本地时间。仅当你必须重建用户的本地挂钟意图(如重复会议)时,才保留单独的 IANA 时区列。