和 Unix 时间打交道： EPOCH

Unix 时间其实就是把时间记录成自 1970 年 1 月 1 日零点（UTC）以来过了多少秒（有时候是毫秒）。这种格式在开发里超常见：存起来方便，传起来快，比大小也简单，因为它就是个数字。

简单说，Unix 时间就像个从 1970 年开始一直在走的计时器。看到类似 1697222400 这种奇怪的数字别慌——这只是从起点到现在过了多少秒。想知道这到底是哪天？马上教你怎么算！

这种格式经常用来系统间同步数据、存时间戳，或者你想快速比比哪个事件早哪个晚的时候。

怎么转成 Unix 时间

PostgreSQL 里有个专门的函数 EXTRACT(EPOCH FROM ...)，能把日期或时间戳转成 Unix 时间。

来，把当前时间转成 Unix 时间：

SELECT EXTRACT(EPOCH FROM NOW());

结果大概像这样：

1697222400

这个结果就是现在距离 1970 年的秒数。

再比如把一个固定日期转成 Unix 时间：

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-10-01 12:00:00');

结果：

1696152000

现在我们知道，2023 年 10 月 1 日中午 12 点（UTC）就是这个值。

怎么从 Unix 时间转回去

当然，也有反过来的转换。如果你手上有 Unix 时间，想把它变成人能看懂的格式，可以用 TO_TIMESTAMP() 函数。

来，把 Unix 时间转成日期和时间：

SELECT TO_TIMESTAMP(1697222400);

结果：

2023-10-13 00:00:00+00

现在我们看到，这就是 2023 年 10 月 13 日 00:00（UTC）。

Unix 时间的实际用法

假设我们有个 events 表，里面存着事件信息，包括 Unix 时间格式的时间戳。

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_time BIGINT -- 用来存 Unix 时间
);

我们可以往这个表里插数据，时间戳直接用 Unix 时间：

INSERT INTO events (event_name, event_time)
VALUES
('服务器启动', 1697222400),
('数据库更新', 1697308800);

想把这些信息变成好读的格式，只要用 TO_TIMESTAMP：

SELECT event_name, TO_TIMESTAMP(event_time) AS readable_time
FROM events;

结果：

小技巧和常见坑

1. 精度选错了

如果你的 Unix 时间是毫秒（比如 1697222400000），不能直接丢给 TO_TIMESTAMP 或 EXTRACT。这种情况要先除以 1000：

SELECT TO_TIMESTAMP(1697222400000 / 1000);

结果：

2023-10-13 00:00:00+00

2. 忘了时区

Unix 时间默认是 UTC，所以结果总是没考虑时区，除非你自己转一下。比如：

SELECT TO_TIMESTAMP(1697222400) AT TIME ZONE 'Europe/Moscow';

结果：

2023-10-13 03:00:00

3. 数字太大出问题

有时候开发者会不小心传了个超大的 Unix 时间（比如毫秒当成秒）。这样会出错：

SELECT TO_TIMESTAMP(1697222400000); -- 会报错！

要修正的话，记得除以 1000。

例子：算任务耗时

举个实际点的例子：我们有个 tasks 表，想算每个任务从创建到现在过了多久。

先建表：

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    task_name TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

插几条任务：

INSERT INTO tasks (task_name)
VALUES
('任务 1'),
('任务 2'),
('任务 3');

现在我们可以加一列 Unix 时间：

SELECT id, task_name, EXTRACT(EPOCH FROM created_at) AS created_epoch
FROM tasks;

结果：

再算下每个任务创建到现在过了多少秒：

SELECT id, task_name, EXTRACT(EPOCH FROM NOW()) - EXTRACT(EPOCH FROM created_at) AS elapsed_seconds
FROM tasks;

结果：

啥时候用 Unix 时间？为啥用？

Unix 时间特别适合在系统间传递时间数据、比时间戳、还有各种需要省 CPU 的场景。API、Web 开发、数据分析、前后端同步啥的都爱用。不过要注意它的特点：默认 UTC、单位可能是秒也可能是毫秒、存储格式也要看清楚。

如果你要和外部系统对接，而它们“说的是 Unix 时间”，现在你已经知道怎么在 PostgreSQL 里搞定了。放心用 TO_TIMESTAMP 和 EXTRACT(EPOCH) 来互相转换，所有时间相关的需求都能轻松搞定！