为什么需要这篇
时间戳几乎支撑了所有查询：排序、范围过滤、日志联表。不同数据库的处理方式却不一样。本文给出快速决策、可直接抄用的建表与转换 SQL，并结合常见坑位，帮助你在 MySQL、PostgreSQL、SQLite 中把时间戳存得对、查得快。
快速决策
- 统一存 UTC，展示时再做本地化。
- 精度选择：分析类秒级即可，日志/追踪选毫秒或更高。
- 类型选择：能用带时区的类型就用它；跨系统互通时可存
BIGINTepoch。 - 索引：避免在列上套函数；需要派生列就用生成列/计算列。
- 留存策略：按时间分区或定期清理，避免索引膨胀。
各引擎推荐做法
MySQL
- 首选
TIMESTAMP（范围 1970-2038）或DATETIME（范围更广），统一存 UTC。 - 需要更高精度或跨 2038 年：用毫秒/微秒级的
BIGINTepoch。
SQL1CREATE TABLE events ( 2 id BIGINT PRIMARY KEY AUTO_INCREMENT, 3 occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 4 occurred_at_ms BIGINT GENERATED ALWAYS AS (UNIX_TIMESTAMP(occurred_at) * 1000) STORED, 5 INDEX idx_events_occurred_at (occurred_at) 6);
PostgreSQL
- 首选
TIMESTAMPTZ（存 UTC，按时区渲染）。 - 精度可到微秒；可用生成列暴露 epoch。
SQL1CREATE TABLE events ( 2 id BIGSERIAL PRIMARY KEY, 3 occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), 4 occurred_at_ms BIGINT GENERATED ALWAYS AS (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT STORED, 5 INDEX (occurred_at) 6);
SQLite
- 内部不强制类型，推荐
INTEGER存 UTC 毫秒 epoch。
SQL1CREATE TABLE events ( 2 id INTEGER PRIMARY KEY, 3 occurred_at_ms INTEGER NOT NULL, -- 统一存 UTC 毫秒 4 created_at DATETIME DEFAULT CURRENT_TIMESTAMP 5); 6CREATE INDEX idx_events_occurred_at_ms ON events (occurred_at_ms);
epoch 与可读时间互转
MySQL
SQL1-- epoch(秒) -> 时间戳 2SELECT FROM_UNIXTIME(1704067200) AS utc_time; 3-- 时间戳 -> epoch 秒/毫秒 4SELECT UNIX_TIMESTAMP(occurred_at) AS epoch_s, 5 UNIX_TIMESTAMP(occurred_at) * 1000 AS epoch_ms 6FROM events; 7-- 安全地应用时区 8SELECT CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') FROM events;
PostgreSQL
SQL1-- epoch 秒 -> timestamptz (UTC) 2SELECT to_timestamp(1704067200) AT TIME ZONE 'UTC'; 3-- timestamptz -> epoch 秒/毫秒 4SELECT EXTRACT(EPOCH FROM occurred_at) AS epoch_s, 5 (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT AS epoch_ms 6FROM events; 7-- 按指定时区展示 8SELECT occurred_at AT TIME ZONE 'America/New_York' FROM events;
SQLite
SQL1-- epoch 毫秒 -> UTC datetime 字符串 2SELECT datetime(occurred_at_ms / 1000, 'unixepoch') AS utc_time FROM events; 3-- UTC datetime -> epoch 毫秒 4SELECT 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)或 epochBIGINT。 - 会话时区漂移：连接后强制
time_zone='+00:00'（MySQL）或SET TIMEZONE='UTC';（PostgreSQL）。 - 精度混用：不要把秒级和毫秒级混在一列，入口处按长度校验。
- 函数包裹索引列：
WHERE DATE(occurred_at)=...会失效索引；改为生成列或范围过滤。 - DST 报表偏差：存 UTC，展示层用真实 IANA 时区再做本地化。
迁移清单
- 冻结写入 或通过双写通道缓冲。
- 新增 UTC 列（如
occurred_at_utc TIMESTAMPTZ或occurred_at_ms BIGINT）。 - 回填：用确定性的转换脚本，并抽样校验。
- 加索引并切读流量 到新列。
- 移除旧列，观察一段时间后收尾。
FAQ
- 存 epoch 还是 datetime? 需要跨系统与高精度时选 epoch
BIGINT；要用数据库内置时间函数和可读性时选TIMESTAMPTZ。 - 精度怎么定？ 日志/监控/链路追踪用毫秒或更高；业务/报表多为秒级足够。
- 如何避免用户传本地时间？ 入口接受带时区的时间，立即转成 UTC 存储。
- 如何校验输入时间戳？ 数字校验 + 长度判定精度 + 合理范围（如 2000-01-01 至 2100-01-01）。参考 时间戳精度层级。