时区与时间戳
存储 UTC、转换时区，并了解面试官经常追问的时间戳陷阱。
时区与时间戳 是 CoddyKit 上的免费 SQL Interview Prep 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Interview Prep 课程共包含 4 节课。
为什么时区会让候选人失分
时区是自信的候选人也容易失手的地方，因此面试官会借此探查深度。核心问题始终是：“如何存储和比较跨地区的时间戳？”
专业的回答是一套规范，而不是某个函数：将所有内容存储为 UTC，仅在显示时在边界处进行转换。存储模型正确后，大多数查询都会变得简单。
- 时间戳与带时区的时间戳
- 在时区之间进行转换
- 将 UTC 作为唯一事实来源
时间戳与带时区时间戳的区别
PostgreSQL 有两种时间戳类型，混淆它们是面试中最常见的失误之一。
timestamp（不带时区）：一种不附带时区的本地钟表时间值，会原样存储输入值。timestamptz（带时区）：内部以 UTC 存储；输入时从会话时区转换，输出时再转换回来。
尽管名称如此，timestamptz 并不存储时区；它存储的是 UTC 中的精确时刻。这个细节会给面试官留下深刻印象。
CREATE TABLE events (
id bigint,
occurred_at timestamptz -- recommended: an absolute instant
);存储 UTC，在边界处转换
黄金法则：将时刻以 UTC 持久化（使用 timestamptz），并且仅在向用户展示时转换为本地时区。这样可以避免夏令时带来的歧义，并确保在任何地方按时间排序都是正确的。
如果被问到“为什么使用 UTC？”，可以这样回答：UTC 没有夏令时调整，因此同一个钟表时间不会像本地时间那样出现两次或被跳过。
-- Display a UTC instant in a user's zone (Postgres)
SELECT occurred_at AT TIME ZONE 'America/New_York' AS local_time
FROM events;AT TIME ZONE 的双重含义
AT TIME ZONE 很巧妙，也经常成为易错点，因为它会根据输入类型执行两种相反的操作：
- 应用于
timestamptz时，它会将绝对时刻转换为该时区的时间，并返回普通的timestamp（该地的本地时钟时间）。 - 应用于普通的
timestamp时，它会将该本地时钟时间解释为位于该时区，并返回timestamptz。
弄清楚它的转换方向，就是关键所在。
-- timestamptz -> local wall clock (returns timestamp)
SELECT TIMESTAMPTZ '2024-03-01 12:00:00+00'
AT TIME ZONE 'Asia/Tokyo'; -- 2024-03-01 21:00:00
-- plain timestamp interpreted in a zone (returns timestamptz)
SELECT TIMESTAMP '2024-03-01 12:00:00'
AT TIME ZONE 'Asia/Tokyo'; -- 2024-03-01 03:00:00+00获取当前时刻
请熟悉各种表示“现在”的函数。NOW() 和 CURRENT_TIMESTAMP 在 Postgres 中返回 timestamptz。它们返回的是事务开始时刻，而不是语句开始时刻；在长事务中，这一点很重要。
如需明确使用 UTC，请进行转换：NOW() AT TIME ZONE 'UTC'。在 MySQL 中，UTC_TIMESTAMP() 会直接给出 UTC。
SELECT
NOW() AS tx_start_tz,
NOW() AT TIME ZONE 'UTC' AS utc_walltime;夏令时才是真正的敌人
面试官很喜欢考察 DST 的边界情况。时钟向前调时，某个本地时钟小时并不存在；向后调时，某个小时会重复。存储本地时间会使这些时刻变得含糊或无效。
存储 UTC 可以完全避开这个问题：每个时刻都是唯一且单调递增的。使用 'America/New_York' 这样的区域名称（而不是 -05:00 这样的固定偏移量），数据库就能针对任意日期正确应用 DST 规则。
-- Region name applies DST automatically for the given date
SELECT TIMESTAMPTZ '2024-07-01 12:00:00+00'
AT TIME ZONE 'America/New_York' AS summer, -- EDT (-04)
TIMESTAMPTZ '2024-01-01 12:00:00+00'
AT TIME ZONE 'America/New_York' AS winter; -- EST (-05)按各时区的本地日期分组
一个现实中的问题是：“按每位用户的本地时间统计日活跃用户。”如果直接截断 UTC 时间戳，对于非 UTC 用户来说，午夜边界就会错位。
请在截断到日期之前先转换为用户所在的时区。转换会调整本地时钟时间，使日期边界与本地时间保持一致。
SELECT
DATE_TRUNC('day', occurred_at AT TIME ZONE u.tz) AS local_day,
COUNT(DISTINCT e.user_id) AS dau
FROM events e
JOIN users u ON u.id = e.user_id
GROUP BY 1
ORDER BY 1;安全比较时间戳
筛选 timestamptz 列时，请与明确的时刻进行比较，最好使用 UTC 字面量或带偏移量的 timestamptz。与不带明确时区的字符串比较时，字符串可能会按照不可预测的会话时区进行解释。
这样无论谁运行查询，比较都不会产生歧义。
SELECT *
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2024-03-01 00:00:00+00'
AND occurred_at < TIMESTAMPTZ '2024-04-01 00:00:00+00';纪元时间与 Unix 时间戳
许多系统将时间存储为 Unix 纪元（自 1970-01-01 UTC 起经过的秒数）。面试官可能会给您一个整数列，让您将其读作时间。
- Postgres：
TO_TIMESTAMP(epoch_seconds)返回timestamptz。 - 转换回纪元秒数：
EXTRACT(EPOCH FROM occurred_at)。 - MySQL：
FROM_UNIXTIME()和UNIX_TIMESTAMP()。
纪元值本质上使用 UTC，这也是它们适合存储的原因之一。
SELECT
TO_TIMESTAMP(1709294400) AS as_ts, -- from epoch
EXTRACT(EPOCH FROM NOW())::bigint AS as_epoch; -- to epoch跨方言时区要点
快速梳理一下，确保您在任何环境中都能表达自如：
- Postgres：
timestamptz加AT TIME ZONE，支持最完善。 - MySQL：
TIMESTAMP会通过会话time_zone自动转换；CONVERT_TZ(t, from, to)用于显式转换。DATETIME不感知时区。 - SQL Server：
datetimeoffset存储偏移量；AT TIME ZONE 'name'使用 Windows 时区名称进行转换。
-- MySQL explicit conversion
SELECT CONVERT_TZ(event_dt, 'UTC', 'Europe/Istanbul') AS local_dt
FROM events;跨越午夜的会话：深入示例
一个容易被忽略的报表问题是：当会话可能跨越午夜时，如何按本地日历日统计会话数量。解决方法仍然是遵循同一原则：先转换为本地时间，再进行分桶。
将开始和结束时间存储为 timestamptz；生成报表时，从转换后的开始时间推导本地日期。如果会话必须拆分到两个日期中，您可以连接一个日期序列表；这也是后续追问时值得提出的一点。
SELECT
DATE_TRUNC('day', started_at AT TIME ZONE 'Europe/Istanbul') AS local_day,
COUNT(*) AS sessions
FROM sessions
GROUP BY 1
ORDER BY 1;快速检查
请确认推荐的存储策略及其原因。
回顾：时区与时间戳
请牢记以下原则：
- 将 UTC 以
timestamptz的形式存储，仅在显示时转换为指定区域。 - 尽管名称如此，
timestamptz存储的是 UTC 时刻，而不是时区。 AT TIME ZONE会根据输入类型执行双向操作：将 timestamptz 转换为本地时钟时间，或将普通 timestamp 解释为位于某个时区。- 使用区域名称（
'America/New_York'），让系统自动应用 DST；避免使用固定偏移量。 - 在截断到日期之前先转换为本地时间，并将列与明确的 UTC 时刻进行比较。
常见问题解答
「时区与时间戳」课时是免费的吗？
是的 — 「时区与时间戳」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 SQL Interview Prep 课程共包含 4 节课。
「时区与时间戳」这节课中我会学到什么？
存储 UTC、转换时区，并了解面试官经常追问的时间戳陷阱。 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 SQL Interview Prep 需要有经验吗？
无需任何先前经验。CoddyKit 上的 SQL Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。
「时区与时间戳」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 SQL Interview Prep 课中编写并运行代码吗？
能。每节 SQL Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。