在数据库中存储时间戳: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 列,以便可读地调试

数据库时间戳常见问题

MongoDB stores dates natively as the BSON Date type — a signed 64-bit integer of milliseconds since the Unix epoch. The driver maps it to a Date object in JavaScript, datetime in Python, ISODate in the mongo shell.

• BSON Date: 8 bytes, signed 64-bit, milliseconds since 1970 UTC
• Indexes natively — range queries on Date are fast and idiomatic
• Use ISODate("2024-01-01T00:00:00Z") in the shell to create comparable values
• For multi-precision needs, use Decimal128 or a sub-document with seconds + nanos; Date itself is millisecond precision
• TTL indexes operate on Date fields specifically — integer epoch fields will not work with expireAfterSeconds

• 转换数据库 epoch 值
• 2038 年问题
• 秒 vs 毫秒指南
• 应避免的 7 个时间戳 bug
• SQL 时间戳代码片段

DynamoDB: epoch attributes and TTL

DynamoDB has no dedicated date type. Two patterns dominate: ISO 8601 strings for human readability, or epoch numbers for compactness and TTL support.

• TTL requires the attribute to be a Number representing Unix seconds (not milliseconds)
• Strings store ISO 8601 with full timezone info but cost more bytes and sort lexicographically (so include the Z suffix consistently)
• Range queries on a sort-key epoch number are extremely cheap and align with DynamoDB’s query model
• Avoid mixing seconds and milliseconds across tables — DynamoDB will not protect you from the unit drift
• DynamoDB Streams emit an ApproximateCreationDateTime in ISO 8601 regardless of how you stored the original value