怎么在数据库里存 Unix 时间戳

#从产品含义出发去挑类型

先想清楚这个值在产品里是什么意思，再去挑数据库类型。已经发生过的事件是一个瞬间；而周期性会议或门店营业时间是一条钟面规则。把这两者当成同一种时间戳，带来的麻烦远比“选 BIGINT 还是原生日期类型”要大。

数据库	默认选择	理由
PostgreSQL	timestamptz	存的是一个瞬间；日期函数强大
MySQL	存 UTC 的 DATETIME(3) 或 DATETIME(6)	绕开 MySQL TIMESTAMP 的 2038 和会话时区陷阱
SQLite	INTEGER 纪元值或 TEXT ISO 8601	没有原生日期类型；选定一种格式并写进文档
MongoDB	BSON Date	原生的 64 位有符号毫秒瞬间
DynamoDB	TTL 用 Number 纪元秒；排序键用 ISO 字符串或数字	TTL 强制要求 Unix 秒
Redis	有序集合分值用纪元秒或毫秒	时间窗口范围查询很快
TimescaleDB	timestamptz 时间列	PostgreSQL 语义加上按时间分区
ClickHouse	DateTime64(3/6/9)	精度可选的分析型时间戳

最简单的规则：

确切事件存成 UTC 瞬间。
本地排期存成「本地日期时间 + IANA 时区」。
用整数时，把纪元单位写进字段名。

#先决定这个时间戳到底意味着什么

数据库里大多数时间戳 bug，早在你选列类型之前就埋下了：团队还没决定这个值究竟是一个精确瞬间，还是一条本地钟面规则。

产品含义	该存什么
“这行记录是在这个确切瞬间创建的”	UTC 瞬间：原生时间戳或纪元整数
“这笔付款在这个确切瞬间过期”	UTC 瞬间 + 写明的单位
“按查看者所在时区展示这条审计事件”	UTC 瞬间；展示时再转换
“每周一纽约时间上午 9 点开会”	weekday、local_time、time_zone
“生日或节日，不含具体时刻”	纯日期值，而不是 UTC 零点
“让 DynamoDB 删掉这一项”	TTL 属性里的 Number 型纪元秒
“让 Redis 查最近 60 秒的事件”	有序集合分值用纪元秒或毫秒

别把一个格式化后的本地字符串当成唯一事实：

2026-06-20 09:00

这个值是不完整的。它可能指 UTC、服务器本地时间、纽约时间、东京时间，或者别的什么。

存瞬间：

{
  "created_at": "2026-06-20T14:30:00Z",
  "created_at_ms": 1781965800000
}

而对于周期性的本地规则，存的是钟面意图：

{
  "weekday": "Monday",
  "local_time": "09:00",
  "time_zone": "America/New_York"
}

#原生时间戳、BIGINT，还是字符串？

大体上有三种存储模式。

模式	擅长	要当心
原生时间戳类型	SQL 日期运算、索引、可读的查询、日期截断	各数据库自己的时区行为
BIGINT 纪元值	事件管道、JavaScript 毫秒、紧凑的数值区间查询	单位搞错：秒 / 毫秒 / 微秒
ISO 8601 字符串	日志、导出、人工查看、没有日期类型的系统	日期运算弱；格式不统一会毁掉排序

业务数据库通常首选原生时间戳类型，因为数据库知道这个值是“时间”：你可以索引它、比较它、按天分组、截断到月，各种时间函数都能直接用。

而当周边系统本来就说“纪元整数”这门语言时，BIGINT 最合适，比如：

• 来自 Date.now() 的 created_at_ms
• 给 DynamoDB TTL 用的 expires_at_seconds
• 来自遥测的 event_time_ns
• Redis 有序集合里的 score

字符串只有在满足这些条件时才可接受：严格的 ISO 8601 或 RFC 3339、一致地补零、并且明确标注是 UTC 或者带偏移：

2026-06-20T14:30:00Z
2026-06-20T10:30:00-04:00

真正的陷阱是自由格式字符串：

Jun 20, 2026 9:25am
06/20/26 09:25
Saturday morning

这类字符串当键不行、做过滤不行、当迁移输入更不行。

#MySQL：DATETIME、TIMESTAMP 还是 BIGINT

新建的 MySQL 业务表，保守的默认选择是存 UTC 值的 DATETIME(3) 或 DATETIME(6)。

示例：

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  created_at DATETIME(3) NOT NULL,
  paid_at DATETIME(3) NULL,
  created_at_ms BIGINT NULL,
  CHECK (created_at >= '2000-01-01 00:00:00')
);

为什么不干脆一律用 TIMESTAMP？

MySQL 文档写明了三点重要差异：

类型	范围	时区行为	存储
TIMESTAMP	1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC	写入时从会话时区转成 UTC，读取时再转回来	4 字节加小数秒
DATETIME	1000-01-01 00:00:00 到 9999-12-31 23:59:59	原样存你给的字面值	5 字节加小数秒
BIGINT	64 位有符号整数	没有任何时区语义	8 字节

TIMESTAMP 用在历史数据和生命周期很短的运维数据上没问题，但对订阅、排定事件、法律记录，或者任何可能跨过 2038 的场景，把它当默认值就很冒险。

如果你确实要用 TIMESTAMP，请把连接时区钉死：

SET time_zone = '+00:00';

如果用 DATETIME，就让“存 UTC”这个约定在代码评审里一眼可见：

created_at_utc DATETIME(3) NOT NULL

如果 JavaScript 客户端直接写事件，配一个平行的整数列会有帮助：

created_at_ms BIGINT NOT NULL

然后给它加校验：

CHECK (created_at_ms BETWEEN 946684800000 AND 4102444800000)

这个范围对应纪元毫秒下的 2000-01-01 到 2100-01-01，能抓出很多“秒当毫秒”的错误。

#PostgreSQL：瞬间就用 timestamptz

PostgreSQL 的 timestamp with time zone（通常写作 timestamptz）是存确切事件时间的正确默认值。

示例：

CREATE TABLE events (
  id bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  event_time timestamptz NOT NULL,
  user_time_zone text NULL
);

这个名字容易让人误会：timestamptz 并不存储原始的时区标签。PostgreSQL 存的是瞬间，并按当前会话时区把它显示出来。如果你插入 2026-06-20 09:00:00 America/New_York，PostgreSQL 能把瞬间解析出来，但它不会记住用户当初填的是 America/New_York。

所以当本地语境重要时，请用两列：

CREATE TABLE meetings (
  id bigserial PRIMARY KEY,
  starts_at timestamptz NOT NULL,
  local_date date NOT NULL,
  local_time time NOT NULL,
  time_zone text NOT NULL
);

Postgres 里的常用操作：

-- 从 timestamptz 取 Unix 秒
SELECT extract(epoch FROM created_at) AS created_at_seconds
FROM events;

-- Unix 秒转回 timestamptz
SELECT to_timestamp(1700000000);

-- 用纽约的钟面时间显示某个瞬间
SELECT created_at AT TIME ZONE 'America/New_York'
FROM events;

区间请用左闭右开：

WHERE created_at >= $1
  AND created_at <  $2

这样就不会漏掉当天末尾那些带微秒的记录。

#SQLite：有意识地在 TEXT 和 INTEGER 之间选

SQLite 没有专门的日期时间类型。官方文档给出了三种存储形式：

• TEXT：ISO 8601 日期时间字符串
• INTEGER：Unix 时间戳
• REAL：儒略日数

对小型应用、本地数据库或移动端来说，下面两种都合理：

CREATE TABLE events_text (
  id INTEGER PRIMARY KEY,
  created_at TEXT NOT NULL
);

CREATE TABLE events_epoch (
  id INTEGER PRIMARY KEY,
  created_at_seconds INTEGER NOT NULL
);

查询示例：

SELECT datetime(created_at_seconds, 'unixepoch')
FROM events_epoch;

SELECT unixepoch(created_at)
FROM events_text;

如果人会直接翻看 .sqlite 文件，而且你始终写入 2026-06-20T14:30:00Z 这样的 UTC 字符串，那就用 TEXT。

如果区间查询和紧凑存储更要紧，就用 INTEGER：

CREATE INDEX events_created_at_seconds_idx
ON events_epoch(created_at_seconds);

同一列里千万别混着放秒和毫秒。想用毫秒，就在名字上写清楚：

created_at_ms INTEGER NOT NULL

#MongoDB：业务时间戳用 BSON Date

MongoDB 原生的 Date 是一个 64 位有符号整数，表示自 Unix 纪元以来的毫秒数。这让它天然适合做业务时间戳。

示例文档：

db.orders.insertOne({
  createdAt: new Date("2026-06-20T14:30:00Z"),
  status: "paid"
});

区间查询：

db.orders.find({
  createdAt: {
    $gte: ISODate("2026-06-01T00:00:00Z"),
    $lt: ISODate("2026-07-01T00:00:00Z")
  }
});

TTL 索引：

db.sessions.createIndex(
  { expiresAt: 1 },
  { expireAfterSeconds: 0 }
);

createdAt、updatedAt、expiresAt 和事件时间都用 BSON Date。只有在确实需要一个独立的兼容值时，才加整数字段：

{
  createdAt: ISODate("2026-06-20T14:30:00Z"),
  createdAtMs: NumberLong("1781965800000")
}

另外别把 BSON Date 和 MongoDB 内部的 BSON Timestamp 类型搞混。MongoDB 文档写明后者是内部类型，应用代码一般应该用 BSON Date。

#DynamoDB：TTL 只认纪元秒

DynamoDB 没有专门的 datetime 类型，你只能在 String 和 Number 之间选。

TTL 的规则很严格：TTL 属性必须是一个 Number，内容是以秒为单位的 Unix 纪元时间。字符串属性会被 TTL 流程直接忽略。而一个 13 位的毫秒值虽然也是数字，但 DynamoDB 会把它当成秒来解释——于是这一项根本不会在你预期的时候过期。

示例项：

{
  "pk": "session#123",
  "created_at": "2026-06-20T14:30:00Z",
  "created_at_ms": 1781965800000,
  "expires_at_seconds": 1781969400
}

DynamoDB 的常见模式：

需求	属性
TTL 删除	expires_at_seconds，Number 型
人可读的导出	以 Z 结尾的 ISO 8601 字符串
按时间做排序键	定宽的 ISO 字符串或纪元数字
JavaScript 客户端兼容	纪元毫秒，Number 型

如果拿 ISO 字符串做排序键，格式必须固定：

2026-06-20T14:30:00Z
2026-06-20T14:31:00Z
2026-06-20T14:32:00Z

这样才排得对。格式一混就不行了。

#Redis：用有序集合做时间窗口

Redis 通常不是你的事实来源数据库，但它做时间窗口查询非常好用。

滑动窗口限流：

ZADD user:123:requests 1781965800000 request-id-1
ZREMRANGEBYSCORE user:123:requests -inf 1781965740000
ZCOUNT user:123:requests 1781965740000 1781965800000
EXPIRE user:123:requests 120

需要毫秒分辨率时，用纪元毫秒当有序集合的分值。Redis 的分值是双精度浮点数：当下的纪元毫秒作为整数完全精确，当下的纪元微秒也仍在 JavaScript 2^53 - 1 安全整数上限之内，但纪元纳秒就不行了。真需要纳秒精度的话，请把秒和纳秒拆开存，别把整个纳秒值塞进分值里。

member 的值要保证唯一：

request-id-1
1781965800000:uuid

如果你要的只是过期，用普通的键过期就行：

SET session:123 payload EX 3600

只有当你需要问“起止时间之间发生了什么”时，才用有序集合。

#时序数据库

当主要访问模式就是“按时间”时，才用时序数据库：可观测性、物联网、金融行情、指标、链路追踪，或者高吞吐的事件流。

不同系统的时间戳模型也不一样：

系统	典型的时间戳模型
InfluxDB	line protocol 的时间戳默认是纳秒精度的 Unix 时间，除非另行指定精度
TimescaleDB	PostgreSQL 超表通常用 timestamptz 做时间列
ClickHouse	秒用 DateTime；毫秒、微秒或纳秒精度用 DateTime64(3/6/9)
Prometheus	常见的暴露/写入路径上，样本用毫秒精度的 Unix 时间戳

别把这件事简化成一句“时序数据库都存 int64 纳秒”。有的是，有的不是。精度是摄入约定的一部分，必须写在字段名或表定义旁边。

ClickHouse 表结构示例：

CREATE TABLE events (
  event_time DateTime64(3, 'UTC'),
  service LowCardinality(String),
  value Float64
)
ENGINE = MergeTree
ORDER BY (service, event_time);

TimescaleDB 的形态示例：

CREATE TABLE conditions (
  time timestamptz NOT NULL,
  device text NOT NULL,
  temperature double precision
);

#给时间戳列建索引

时间戳查询大多是范围查询。先照着这一点来设计。

好的写法：

WHERE created_at >= '2026-06-01T00:00:00Z'
  AND created_at <  '2026-07-01T00:00:00Z'

有风险的写法：

WHERE created_at BETWEEN '2026-06-01' AND '2026-06-30'

BETWEEN 是闭区间，而只有日期的字符串还会把“零点”这个假设藏起来。左闭右开在任何精度下都更好推理。

实用的索引：

CREATE INDEX orders_created_at_idx
ON orders (created_at);

CREATE INDEX events_account_time_idx
ON events (account_id, created_at);

只有当表以追加写为主、而且大到分区裁剪确实有意义时，才按时间分区。一个简单的 B-tree 索引，胜过一套糟糕的分区方案。

#迁移检查清单

时间戳迁移翻车，往往是因为团队换了存储类型，却没有证明那个瞬间没变。

按这个流程来：

1. 加新列。
2. 分批回填。
3. 把新旧值都当作 UTC 瞬间来比对。
4. 双写一个版本周期。
5. 把读切到新列。
6. 保留旧列，直到看板、导出和客服工具都对得上。
7. 在后续的迁移里再删掉旧列。

示例：

-- MySQL：纪元毫秒转 UTC DATETIME(3)
UPDATE events
SET created_at_utc = FROM_UNIXTIME(created_at_ms / 1000.0)
WHERE created_at_utc IS NULL;

-- PostgreSQL：纪元毫秒转 timestamptz
UPDATE events
SET created_at = to_timestamp(created_at_ms / 1000.0)
WHERE created_at IS NULL;

-- SQLite：纪元秒转类 ISO 的 UTC 文本
UPDATE events
SET created_at_text = datetime(created_at_seconds, 'unixepoch')
WHERE created_at_text IS NULL;

回填之后，抽样检查这些边界情况：

• 纪元 0
• 如果你的业务里存在负时间戳，也要查
• 2038-01-19 03:14:07 UTC
• 主要用户地区的夏令时切换点
• 那些一旦被当成秒就会变成 55000 年的毫秒值

#数据库里常见的时间戳错误

错误做法	症状	怎么改
created_at 是存 Unix 秒的 INT 列	接近 2038 的未来日期失败	改用 BIGINT 或原生时间戳
用 MySQL TIMESTAMP 存订阅到期时间	2038 之后的值存不下	改用 UTC 的 DATETIME(3) 或 BIGINT
指望 Postgres 的 timestamptz 记住 America/New_York	原始时区已经丢了	另外存一列 time_zone
DynamoDB TTL 存成了毫秒	项目不在预期时间过期	存 Number 型的纪元秒
Redis 有序集合分值用纪元纳秒	精度损失	用毫秒，或把秒和纳秒拆开
SQLite 某列混着放秒和毫秒	日期落到 1970 年或 55000 年	列名带单位并加校验
ISO 字符串没有补零	字典序排序失效	用严格的 RFC 3339 / ISO 8601
拿本地显示字符串当事实来源	夏令时和时区漂移	存 UTC 瞬间 + 时区上下文

列名是表结构约定的一部分，不是装饰。优先用：

created_at
created_at_ms
expires_at_seconds
event_time_ns
time_zone

而不是：

timestamp
date
time

#按场景推荐的写法

场景	推荐的表结构
PostgreSQL 业务事件	created_at timestamptz NOT NULL DEFAULT now()
MySQL 业务事件	created_at_utc DATETIME(3) NOT NULL
JavaScript 侧摄入	created_at_ms BIGINT NOT NULL，并加单位校验
MongoDB 文档	createdAt: Date
SQLite 本地应用	created_at_seconds INTEGER 或 created_at TEXT
DynamoDB 会话	TTL 用 expires_at_seconds，Number 型
Redis 滑动窗口	ZSET 分值用纪元毫秒
周期性本地排期	本地日期时间字段 + IANA 的 time_zone
不含时刻的生日或到期日	用 DATE，而不是零点的时间戳
可观测性指标	时序数据库，并声明精度

#官方参考资料

• MySQL DATETIME 与 TIMESTAMP
• MySQL 数据类型存储需求
• PostgreSQL 日期时间类型
• PostgreSQL 日期时间函数
• SQLite 日期时间函数
• MongoDB BSON Date
• MongoDB TTL 索引
• DynamoDB TTL
• Redis 有序集合
• InfluxDB line protocol
• Timescale 超表
• ClickHouse DateTime64

#相关指南

• Unix 时间转日期
• 日期转 Epoch
• 什么是 UTC 时间
• 毫秒还是秒
• JavaScript 的 Date.now()
• JavaScript 时区指南
• 时间戳 bug
• 2038 年问题