SQL Academy · 课时

时区：TIMESTAMP 与 TIMESTAMPTZ

理解 TIMESTAMP 与 TIMESTAMPTZ 的区别，使用 AT TIME ZONE 进行转换，并存储 UTC 以支持可移植应用。

时区：TIMESTAMP 与 TIMESTAMPTZ 是 CoddyKit 上的免费 SQL Academy 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

两种时间戳类型

PostgreSQL 有 TWO 种时间戳类型：

TIMESTAMP — “朴素”时间戳，不含时区信息
TIMESTAMPTZ — 带时区的时间戳（内部以 UTC 存储）

经验法则：事件优先使用 TIMESTAMPTZ；对于“上午 9:00 的营业时间”这类纯抽象时间，使用 TIMESTAMP。

TIMESTAMPTZ 的工作方式

内部始终以 UTC 存储。执行 INSERT 时，输入值会从会话 TZ 转换为 UTC。执行 SELECT 时，输出值会转换回会话 TZ。

SET TIME ZONE 'Europe/Berlin';
INSERT INTO events (ts) VALUES ('2024-03-15 12:00:00');
-- stored as 2024-03-15 11:00:00 UTC (Berlin is UTC+1 in winter)

SET TIME ZONE 'UTC';
SELECT ts FROM events;
-- 2024-03-15 11:00:00+00

TIMESTAMP 会丢失信息

普通 TIMESTAMP 不知道时区。“2024-03-15 12:00:00”可能表示任意当地时间。当用户更换时区后，原本的含义就丢失了。

AT TIME ZONE

转换时区以便显示：

SELECT ts AT TIME ZONE 'America/New_York' AS ny_time FROM events;
SELECT ts AT TIME ZONE 'Asia/Tokyo'        AS tokyo_time FROM events;

存储 UTC，显示当地时间

最佳实践：存储 TIMESTAMPTZ，在 SELECT 中转换为用户所在的时区，不要反过来做。

SELECT id,
       ts AT TIME ZONE COALESCE(u.tz, 'UTC') AS local_ts
FROM events e
JOIN users u ON u.id = e.user_id;

SESSION 时区

您可以按会话、按用户或全局设置它：

-- Session:
SET TIME ZONE 'Europe/Berlin';

-- Per-user (PG):
ALTER ROLE alice SET timezone TO 'Europe/Berlin';

-- Server default in postgresql.conf:
-- timezone = 'UTC'

在 LITERAL 中提供时区

在字面量中嵌入时区：

SELECT TIMESTAMPTZ '2024-03-15 12:00:00 Europe/Berlin';
SELECT '2024-03-15 12:00:00+02'::TIMESTAMPTZ;

NOW() 返回 TIMESTAMPTZ

NOW() 和 CURRENT_TIMESTAMP 始终返回 TIMESTAMPTZ。将它们与 TIMESTAMP 列混用是一个常见错误。

适合使用 TIMESTAMP（无时区）的情况

对于确实不依赖时区的抽象时间或当地时间——例如“商店在 09:00 开门”——请使用 TIMESTAMP（或 TIME）。

CREATE TABLE store_hours (
  store_id BIGINT,
  open_at  TIME NOT NULL,
  close_at TIME NOT NULL
);

DST 转换

TIMESTAMPTZ 会自动处理 DST。添加 INTERVAL 时，除非跨越 DST 边界，否则会按墙上时钟的小时数计算；跨越边界后，时间可能会偏移 23 或 25 小时。

SELECT TIMESTAMPTZ '2024-03-30 23:00:00 Europe/Berlin' + INTERVAL '5 hours';
-- Result accounts for the DST transition on March 31

不要存储偏移量，而要存储时区名称

像 +02:00 这样的偏移量无法告诉您 DST 何时开始。像 Europe/Berlin 这样的时区名称可以。当您存储用户的常用时区时，请存储 IANA 名称。

回顾

事件默认使用 TIMESTAMPTZ。

以 UTC 存储，以会话 TZ 显示
使用 AT TIME ZONE 进行显示转换
仅在抽象的当地时间场景下使用 TIMESTAMP（无时区）

快速检查

您正在构建一个全球 SaaS，需要记录用户事件。occurred_at 列应该使用哪种时间戳类型？

常见问题解答

「时区：TIMESTAMP 与 TIMESTAMPTZ」课时是免费的吗？

是的 — 「时区：TIMESTAMP 与 TIMESTAMPTZ」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「时区：TIMESTAMP 与 TIMESTAMPTZ」这节课中我会学到什么？

理解 TIMESTAMP 与 TIMESTAMPTZ 的区别，使用 AT TIME ZONE 进行转换，并存储 UTC 以支持可移植应用。你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「时区：TIMESTAMP 与 TIMESTAMPTZ」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy