在处理时区时，有时会出现令人困惑的结果，尤其是在结合 Django 的 ORM、PostgreSQL 中的原生 SQL（例如在 PostgreSQL 物化视图中用于性能优化）以及特定的时区需求时。最近，我在按年聚合交通停车数据时遇到了一个问题，所有按年计算的结果都需要反映“America/New_York”（东部标准时间/东部夏令时间）时区，尽管我们最初的数据包含的是带有时区的时间戳字段。我们使用了 django-pgviews-redux 来管理物化视图，而我错误地尝试将时区逻辑应用于一个没有时间或时区信息的日期字段。
问题的核心在于对 PostgreSQL 在处理日期类型时的 EXTRACT 操作与 AT TIME ZONE 结合使用的方式存在误解，尤其是在默认数据库连接为 UTC 的 Django 环境中。
PostgreSQL 对时间戳和时区的处理
PostgreSQL 的 timestamp with time zone（通常缩写为 timestamptz）类型是一种常见的数据库类型，用于存储日期和时间信息。根据 PostgreSQL 文档：
对于带有时区的时间戳值，如果输入字符串中包含显式时区，则会使用该时区的偏移量将其转换为 UTC（协调世界时）。
当你查询一个 timestamptz 列时，PostgreSQL 会将存储的 UTC 值转换回当前会话的 TimeZone。你可以通过 SHOW TIME ZONE; 查看会话的时区。Django 默认将所有数据库连接的会话 TimeZone 设置为“UTC”。这是一个合理的默认值，用于保持一致性，但如果同时通过 psql 或其他客户端（例如在我的 Mac 上通过 Postgres.app 使用时区为“America/New_York”）与数据库交互，可能会导致混淆。
你可以更改会话时区并观察其效果：
tztest=# SHOW TIME ZONE;
-- TimeZone
-- ------------------
-- America/New_York (如果通过我的 Mac 和 Postgres.app 以及 psql 运行)
tztest=# SELECT '2025-01-01 00:00:00 EST'::timestamptz;
-- SET
-- timestamptz
-- ------------------------
-- 2025-01-01 00:00:00-05 (以 UTC 存储，以会话时区 America/New_York 显示)
tztest=# SET TIME ZONE 'UTC'; SELECT '2025-01-01 00:00:00 EST'::timestamptz;
-- SET
-- timestamptz
-- ------------------------
-- 2025-01-01 05:00:00+00 (以 UTC 存储，以会话时区 UTC 显示)
AT TIME ZONE子句用于将带有时区的时间戳转换为指定时区的无时区时间戳，或者将无时区的时间戳转换为带有时区的时间戳，假设该朴素时间戳位于指定时区。
-- 假设会话时区为 UTC
tztest=# SELECT '2025-01-01 05:00:00+00'::timestamptz AT TIME ZONE 'America/New_York';
-- timezone
-- ---------------------
-- 2025-01-01 00:00:00 (结果是无时区的时间戳)
陷阱：使用 AT TIME ZONE 从 DATE 类型中提取子字段
这里是我遇到问题的地方。我的目标是从“America/New_York”时区中提取交通停车的年份。原始数据是 timestamptz 类型，但在构建物化视图的原生 SQL 查询时，经过多个公共表表达式（CTEs）后，我处理的是一个日期类型。
考虑以下场景，这反映了这种混淆：你的应用程序（会话时区为“UTC”）执行如下查询：
-- 会话时区为 'UTC'
SELECT
'2025-01-01'::date AS the_date,
EXTRACT('year' FROM '2025-01-01'::date)::integer AS extract_year_simple,
EXTRACT('year' FROM ('2025-01-01'::date AT TIME ZONE 'America/New_York'))::integer AS extract_year_at_new_york;
你可能期望 extract_year_at_new_york是 2025。然而，结果却是 2024：
-[ RECORD 1 ]------------+-----------
the_date | 2025-01-01
extract_year_simple | 2025
extract_year_at_new_york | 2024
2024？发生了什么？
'2025-01-01'::date 仅仅是 2025 年 1 月 1 日的日期。
当将 AT TIME ZONE 'America/New_York' 应用于这个日期类型时，PostgreSQL 会隐式地将日期转换为当前会话时区（UTC）的当天开始时间的时间戳。因此，‘2025-01-01’::date 变成了 2025-01-01 00:00:00 UTC。
然后，2025-01-01 00:00:00 UTC 被转换为“America/New_York”时区。2025-01-01 00:00:00 UTC 实际上是 2024-12-31 19:00:00 EST（UTC-5）。
从 2024-12-31 19:00:00 EST 中提取年份，正确地得到了 2024。
这种行为发生是因为对日期类型（或带有时区的时间戳）应用 AT TIME ZONE 时，会根据会话时区进行转换。
在我的案例中，我在物化视图中按年聚合日期，并错误地使用了 AT TIME ZONE 'America/New_York'来提取年份，这在 Django 中聚合数据时导致了错误的结果，因为 1 月 1 日的交通停车被错误地归入了错误的年份，导致按年分组的计数与其他按年分组的查询结果不一致。
我在调试问题时感到困惑，因为我使用的是 psql，会话时区设置为“America/New_York”，这使得 EXTRACT 看起来按我预期工作。直到我切换到 UTC 会话时，问题才变得清晰。
例如，当在会话时区设置为“America/New_York”时重新运行上述查询时，提取的年份是一致的：
tztest=# \x
扩展显示已开启。
tztest=# SET TIME ZONE 'America/New_York';
SET
tztest=# SELECT
'2025-01-01'::date
, EXTRACT('year' FROM '2025-01-01'::date)::integer AS extract_year_display_est
, EXTRACT('year' FROM '2025-01-01'::date AT TIME ZONE 'America/New_York')::integer AS extract_year_at_est_display_est;
-[ RECORD 1 ]-------------------+-----------
date | 2025-01-01
extract_year_display_est | 2025
extract_year_at_est_display_est | 2025
结论
在 Django 和 PostgreSQL 中处理时区、日期和时间戳时，了解每个系统如何处理时区非常重要。注意数据库和 Django 中的时区设置，并在从日期和时间戳中提取子字段时要格外小心。希望这篇文章能帮助你避免我在处理时区和日期时遇到的陷阱！
原文地址：https://www.caktusgroup.com/blog/2025/06/16/avoiding-timezone-traps-correctly-extracting-datetime-subfields-django-postgresql/
原文作者：Colin Copeland
