Postgres物化视图中如何基于timestamptz时区指定date_trunc输出时区？

我正在Postgres中创建物化视图，其中需要一列存储每月起始时刻的timestamptz。用常规语句：

select date_trunc('month', t.starttime)

得到的是创建或刷新物化视图时系统时区下的月初时间。

我希望指定月初时间所属的时区，目前可以用字面量字符串指定，比如下面的语句能得到芝加哥时区午夜时刻的月初时间：

select (date_trunc('month', t.tstzcol) at time zone 'America/Chicago')::timestamptz;

但我想把'America/Chicago'替换成表中t.tstzcol对应的时区名称。试过用extract(timezone from t.tstzcol)，但它返回整数，没法用在at time zone语法里。

请问怎么让date_trunc返回指定时区的timestamptz，而不是本地时区的？

解决方法 #

1. 使用表中单独存储的时区名称字段 #

Postgres的timestamptz类型仅存储UTC偏移量，不保留时区名称。如果你的表中单独维护了时区名字段（比如timezone_name varchar，值为'America/Chicago'这类标准时区名），可以直接用该字段替换字面量：

select (date_trunc('month', t.tstzcol) at time zone t.timezone_name)::timestamptz
from your_table t;

2. 通过偏移量匹配时区（可靠性有限） #

如果没有单独存储时区名称，只能通过UTC偏移量反向匹配时区，但这种方法可能存在歧义（不同时区可能有相同偏移，夏令时也会影响结果）。可以借助pg_timezone_names系统视图实现：

select (date_trunc('month', t.tstzcol) at time zone p.tzname)::timestamptz
from your_table t
join pg_timezone_names p 
  on p.utc_offset = extract(timezone from t.tstzcol)::interval;

注意：该语句可能返回多个匹配结果，需要结合业务场景添加过滤条件（比如and p.is_dst = extract(is_dst from t.tstzcol)），建议优先采用第一种方法。

3. 先转时区再截断（更直观的逻辑） #

另一种思路是先将timestamptz转换为目标时区的本地时间，截断到月初后再转回timestamptz，确保截断操作是在目标时区的时间维度上进行：

select (date_trunc('month', t.tstzcol at time zone t.timezone_name) at time zone t.timezone_name)::timestamptz
from your_table t;

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