为何AT TIME ZONE未达预期？时区转换与date_trunc使用问题

咱们先把PostgreSQL里AT TIME ZONE的核心逻辑掰明白，这就是你踩坑的根源：

先搞懂AT TIME ZONE的两种用法 #

PostgreSQL这个操作符的行为完全取决于你输入的时间类型：

• 如果你的时间是带时区的（timestamptz）：timestamptz AT TIME ZONE '时区'会返回不带时区的本地时间（timestamp），简单说就是把UTC时间转成目标时区的当地时间。
  举个例子：'2020-06-05 21:00:00+00'::timestamptz AT TIME ZONE '+3' → 得到东三区的2020-06-06 00:00:00，这正是你想要的对应关系。
• 如果你的时间是不带时区的（timestamp）：timestamp AT TIME ZONE '时区'会反过来——它把这个不带时区的时间当作目标时区的本地时间，转成UTC的带时区时间。
  比如：'2020-06-05 18:00:00'::timestamp AT TIME ZONE '+3' → 得到UTC的2020-06-05 15:00:00+00，这显然不是你要的。

你的问题出在哪？ #

你期望UTC时间2020-06-05 21:00:00被归到东三区的2020-06-06日期组，但结果里UTC2020-06-05 18:00:10就跳到了2020-06-06，这说明你搞反了时区转换的方向：
大概率你的tm字段是不带时区的timestamp类型，而你直接写tm AT TIME ZONE '+3'时，PostgreSQL会把tm当成东三区的本地时间去转UTC，而不是把tm（你以为是UTC）转成东三区时间。这样一来，转换后的时间完全不符合你的业务逻辑，后续的date_trunc自然也错了。

给你两种正确的写法 #

根据你的tm字段类型选对应的方案：

方案1：如果tm是带时区的timestamptz（存储的是UTC） #

先把UTC时间转成东三区本地时间，再按天截断，要是需要带时区的结果，还能再转回去：

SELECT 
  tm,
  -- 得到东三区本地时间的当天0点（不带时区）
  date_trunc('day', tm AT TIME ZONE '+3') AS local_date_trunc,
  -- 可选：转成带时区的东三区时间，方便后续业务使用
  date_trunc('day', tm AT TIME ZONE '+3') AT TIME ZONE '+3' AS tz_aware_date_trunc
FROM scm.tbl 
WHERE tm BETWEEN '2020-06-05 15:00:00+00' AND '2020-06-08 20:59:00+00' 
ORDER BY tm;

这样执行后，UTC2020-06-05 21:00:00会被转成东三区的2020-06-06 00:00:00，date_trunc后正好是你要的日期分组。

方案2：如果tm是不带时区的timestamp（存储的是UTC） #

得先把不带时区的UTC时间标记成带时区的UTC时间，再转成东三区本地时间，最后截断：

SELECT 
  tm,
  -- 先把UTC的timestamp转成带时区的timestamptz，再转东三区时间，最后按天截断
  date_trunc('day', (tm AT TIME ZONE 'UTC') AT TIME ZONE '+3') AS local_date_trunc
FROM scm.tbl 
WHERE (tm AT TIME ZONE 'UTC') BETWEEN '2020-06-05 15:00:00+00' AND '2020-06-08 20:59:00+00' 
ORDER BY tm;

这里的tm AT TIME ZONE 'UTC'是关键——它告诉PostgreSQL：“这个不带时区的时间是UTC时间”，之后的转换就和方案1一致了。

验证结果 #

用上面的SQL执行后，UTC2020-06-05 21:00:00会被分到2020-06-06组，而UTC2020-06-05 20:59:59会留在2020-06-05组，完全符合你的预期。

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