PostgreSQL中如何将VARCHAR类型UTC时间转为带时区TIMESTAMP

核心问题分析 #

你之前的转换逻辑错误在于：直接将VARCHAR类型的UTC时间转成不带时区的timestamp，这会丢失原字符串中Z（代表UTC时区）的时区信息，后续的AT TIME ZONE操作自然无法正确计算Asia/Calcutta（+5:30）的偏移量。

方案一：永久修改列类型（推荐） #

将存储UTC时间的VARCHAR列直接转为带时区的时间戳类型timestamptz，PostgreSQL能自动识别YYYY-MM-DDTHH:MI:SS.FFFZ格式的UTC字符串：

ALTER TABLE dashboard ALTER COLUMN dtm TYPE timestamptz USING dtm::timestamptz;

修改完成后，后续查询加尔各答时区的时间或按该时区日期过滤会更高效：

• 查询加尔各答时区时间：

  SELECT dtm AT TIME ZONE 'Asia/Calcutta' AS calcutta_time FROM dashboard;
  

• 按加尔各答时区的2022-11-30过滤数据：

  SELECT dtm AT TIME ZONE 'Asia/Calcutta' AS calcutta_time
  FROM dashboard
  WHERE (dtm AT TIME ZONE 'Asia/Calcutta')::date = '2022-11-30'
  ORDER BY dtm;
  

方案二：临时查询时正确转换（不修改列） #

如果暂时无法修改列类型，需要在查询中先将字符串转为timestamptz（保留UTC时区信息），再转换为加尔各答时区：

SELECT dtm::timestamptz AT TIME ZONE 'Asia/Calcutta' AS calcutta_time
FROM dashboard
WHERE (dtm::timestamptz AT TIME ZONE 'Asia/Calcutta')::date = '2022-11-30'
ORDER BY dtm::timestamptz;

为什么之前的写法不对？ #

dtm::timestamp会把2022-11-30T17:30:00.000Z解析为不带时区的时间戳（相当于忽略了Z，直接取时间值），此时AT TIME ZONE 'Asia/Calcutta'的逻辑是“将这个不带时区的时间视为加尔各答本地时间，转成UTC时间戳”，完全和你的需求相反，自然得不到正确结果。

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