SQL 片段

SQL Unix 时间戳代码片段

在 MySQL、PostgreSQL 和 SQLite 中处理 Unix 时间戳的 SQL 示例，包括取当前 epoch 值、转成日期，以及转回时间戳。

MySQL — current timestamp

-- Seconds
UNIX_TIMESTAMP()

-- Milliseconds (MySQL 8.0+)
ROUND(UNIX_TIMESTAMP(NOW(3)) * 1000)

MySQL — timestamp to datetime

FROM_UNIXTIME(1700000000)
FROM_UNIXTIME(1700000000, '%Y-%m-%d %H:%i:%S')

MySQL — datetime to timestamp

UNIX_TIMESTAMP('2023-11-15 06:13:20')

PostgreSQL — current timestamp

-- Seconds (integer)
EXTRACT(EPOCH FROM NOW())::BIGINT

-- Milliseconds
ROUND(EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT

PostgreSQL — timestamp to timestamptz

TO_TIMESTAMP(1700000000)
TO_TIMESTAMP(1700000000) AT TIME ZONE 'America/New_York'

PostgreSQL — datetime to epoch

EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2023-11-15T06:13:20Z')::BIGINT

SQLite — current timestamp

strftime('%s', 'now')  -- seconds

SQLite — timestamp to datetime

datetime(1700000000, 'unixepoch')
datetime(1700000000, 'unixepoch', 'localtime')

下面这些 SQL 示例以 Unix 秒作为通用基准，只有在运行时天然提供毫秒的地方才展示毫秒。复制之前先回答一个简单的问题：下游那个 API 要的是什么单位？

获取当前 Unix 时间

-- PostgreSQL
SELECT EXTRACT(EPOCH FROM NOW())::bigint;

-- MySQL
SELECT UNIX_TIMESTAMP();

-- PostgreSQL
SELECT to_timestamp(1700000000);

-- MySQL
SELECT FROM_UNIXTIME(1700000000);

不同数据库引擎的函数行为不一样，还会受会话时区影响。有带时区的列类型就优先用，区间边界也统一用一个声明清楚的单位来定义。

值跨越系统边界时，把单位写进字段名里：createdAtSeconds 和 createdAtMs 是比 createdAt 长了点，但出事故的时候能省下不少猜测。

Q: epoch 毫秒可以存进 INT 吗？: A: 不行。现在的毫秒时间戳有 13 位，32 位 INT 装不下。请用 BIGINT 或数据库原生的时间戳类型。
Q: 数据库里的时间戳一定是 UTC 吗？: A: 不一定。具体行为取决于字段类型和会话时区。跨系统导入、导出和比较时，把 UTC 明确写出来。