如何基于用户时区查询PostgreSQL的timestamptz列？

问题分析 #

你的vs.created_at字段是timestamptz类型，存储值为2023-08-13 11:31:33.013 +0000（UTC时间）。你尝试的两种方法都未命中记录，核心原因是时区转换逻辑错误：

• 方法1失效原因：
  vs.created_at AT TIME ZONE 'America/New_York'会将UTC时间转换为纽约本地时间，结果是2023-08-13 07:31:33.013（夏季EDT时区，UTC-4）。而你把查询的UTC范围2023-08-12T00:00:00.000Z至2023-08-12T23:59:59.999Z转成纽约时间后，对应的是2023-08-11 20:00:00至2023-08-12 19:59:59.999。记录的纽约时间在8月13号，不在该范围内，因此无法命中。

• 方法2失效原因：
  vs.created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York'的逻辑是：先将timestamptz转成UTC本地时间（timestamp类型的2023-08-13 11:31:33.013），再将这个时间当作纽约本地时间转换为timestamptz，最终得到UTC时间2023-08-13 15:31:33.013。而查询条件转换后对应的UTC范围是2023-08-12 04:00:00至2023-08-13 03:59:59.999，记录的UTC时间不在此范围内，因此也无法命中。

正确解决方案 #

场景1：查询纽约时间2023-08-12全天的记录 #

如果你的实际需求是筛选纽约时间落在8月12号的记录（注：你的当前记录纽约时间为8月13号，不在此范围，若要命中需调整记录时间或查询范围），正确写法如下：

-- 用 >= 和 < 避免毫秒边界的遗漏或误判
AND vs.created_at AT TIME ZONE 'America/New_York' >= '2023-08-12 00:00:00'::timestamp
AND vs.created_at AT TIME ZONE 'America/New_York' < '2023-08-13 00:00:00'::timestamp

场景2：查询UTC时间2023-08-12全天的记录 #

如果需求确实是查询UTC时间8月12号的记录，那你的当前记录（UTC8月13号）本身就不在该范围内，无法被返回。此时需要检查记录时间是否正确，或者调整查询的时间范围。

内容的提问来源于stack exchange，提问作者Rami