MakeTimestamp logo
MakeTimestamp

指南

数据库里的时间戳：存储、索引与转换实战指南

为什么需要这篇

时间戳几乎支撑了所有查询：排序、范围过滤、日志联表。不同数据库的处理方式却不一样。本文给出快速决策、可直接抄用的建表与转换 SQL，并结合常见坑位，帮助你在 MySQL、PostgreSQL、SQLite 中把时间戳存得对、查得快。

快速决策

  • 统一存 UTC，展示时再做本地化。
  • 精度选择：分析类秒级即可，日志/追踪选毫秒或更高。
  • 类型选择：能用带时区的类型就用它；跨系统互通时可存 BIGINT epoch。
  • 索引：避免在列上套函数；需要派生列就用生成列/计算列。
  • 留存策略：按时间分区或定期清理，避免索引膨胀。

各引擎推荐做法

MySQL

  • 首选 TIMESTAMP（范围 1970-2038）或 DATETIME（范围更广），统一存 UTC。
  • 需要更高精度或跨 2038 年：用毫秒/微秒级的 BIGINT epoch。
CREATE TABLE events (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  occurred_at_ms BIGINT GENERATED ALWAYS AS (UNIX_TIMESTAMP(occurred_at) * 1000) STORED,
  INDEX idx_events_occurred_at (occurred_at)
);

PostgreSQL

  • 首选 TIMESTAMPTZ（存 UTC，按时区渲染）。
  • 精度可到微秒；可用生成列暴露 epoch。
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  occurred_at_ms BIGINT GENERATED ALWAYS AS (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT STORED,
  INDEX (occurred_at)
);

SQLite

  • 内部不强制类型，推荐 INTEGER 存 UTC 毫秒 epoch。
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  occurred_at_ms INTEGER NOT NULL, -- 统一存 UTC 毫秒
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_events_occurred_at_ms ON events (occurred_at_ms);

epoch 与可读时间互转

MySQL

-- epoch(秒) -> 时间戳
SELECT FROM_UNIXTIME(1704067200) AS utc_time;
-- 时间戳 -> epoch 秒/毫秒
SELECT UNIX_TIMESTAMP(occurred_at) AS epoch_s,
       UNIX_TIMESTAMP(occurred_at) * 1000 AS epoch_ms
FROM events;
-- 安全地应用时区
SELECT CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') FROM events;

PostgreSQL

-- epoch 秒 -> timestamptz (UTC)
SELECT to_timestamp(1704067200) AT TIME ZONE 'UTC';
-- timestamptz -> epoch 秒/毫秒
SELECT EXTRACT(EPOCH FROM occurred_at) AS epoch_s,
       (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT AS epoch_ms
FROM events;
-- 按指定时区展示
SELECT occurred_at AT TIME ZONE 'America/New_York' FROM events;

SQLite

-- epoch 毫秒 -> UTC datetime 字符串
SELECT datetime(occurred_at_ms / 1000, 'unixepoch') AS utc_time FROM events;
-- UTC datetime -> epoch 毫秒
SELECT strftime('%s', '2024-12-01 00:00:00') * 1000 AS epoch_ms;

调试时需要更精确的转换？直接打开 Unix Timestamp Converter 或 Batch Timestamp Converter。

索引与查询模式

  • 用 范围过滤 (WHERE occurred_at >= ... AND occurred_at < ...) 保持可走索引。
  • 避免在列上包 DATE()/CAST()/::date；如需按天分组，提前建生成列。
  • 热点分区 场景，可加复合索引 (occurred_at, user_id) 等按查询顺序排布。
  • 分区/留存：热数据保持小分区，旧分区定期下沉或归档。
  • 排序：ORDER BY occurred_at DESC LIMIT 100 需要以 occurred_at 为索引前缀。

常见坑与修复

  • 2038 限制（MySQL TIMESTAMP）：超范围需求请用 DATETIME(3) 或 epoch BIGINT。
  • 会话时区漂移：连接后强制 time_zone='+00:00'（MySQL）或 SET TIMEZONE='UTC';（PostgreSQL）。
  • 精度混用：不要把秒级和毫秒级混在一列，入口处按长度校验。
  • 函数包裹索引列：WHERE DATE(occurred_at)=... 会失效索引；改为生成列或范围过滤。
  • DST 报表偏差：存 UTC，展示层用真实 IANA 时区再做本地化。

迁移清单

  1. 冻结写入 或通过双写通道缓冲。
  2. 新增 UTC 列（如 occurred_at_utc TIMESTAMPTZ 或 occurred_at_ms BIGINT）。
  3. 回填：用确定性的转换脚本，并抽样校验。
  4. 加索引并切读流量 到新列。
  5. 移除旧列，观察一段时间后收尾。

FAQ

  • 存 epoch 还是 datetime? 需要跨系统与高精度时选 epoch BIGINT；要用数据库内置时间函数和可读性时选 TIMESTAMPTZ。
  • 精度怎么定？ 日志/监控/链路追踪用毫秒或更高；业务/报表多为秒级足够。
  • 如何避免用户传本地时间？ 入口接受带时区的时间，立即转成 UTC 存储。
  • 如何校验输入时间戳？ 数字校验 + 长度判定精度 + 合理范围（如 2000-01-01 至 2100-01-01）。参考 时间戳精度层级。

相关工具与指南