如何从TIMESTAMPTZ中提取对应本地时区的日期？

我有一个timestamp with time zone类型的列，其中存储的值来自不同时区（已知PostgreSQL底层会将所有值转为UTC存储）。我需要提取对应原始输入时区的日期，而非转换为数据库默认时区或会话时区后的日期。

比如，对于值2020-01-01 23:00:00-07，以下查询在会话时区设为UTC时都会返回2020-01-02，但我期望得到2020-01-01：

SET TIMEZONE TO 'UTC';

SELECT '2020-01-01 23:00:00-07'::TIMESTAMPTZ::DATE;
SELECT date('2020-01-01 23:00:00-07'::TIMESTAMPTZ);
SELECT DATE_TRUNC('day', '2020-01-01 23:00:00-07'::TIMESTAMPTZ)::DATE;

修改会话时区不是可行方案，我需要一个独立于会话时区、适用于所有可能时区偏移的通用解法。

核心说明与解决方案 #

首先要明确：PostgreSQL的timestamptz类型仅存储UTC时间戳，不会保留原始输入的时区偏移信息。因此无法直接从已存储的timestamptz值中恢复原始时区的日期，除非你额外存储了对应的时区/偏移数据。

针对你的需求，有两种可行的通用方案：

• 方案一：额外存储时区信息
  在表中新增一列（比如original_tz TEXT），插入数据时同时记录原始输入的时区偏移或时区名称。查询时通过AT TIME ZONE将timestamptz值转换回原始时区，再提取日期：

  -- 示例表结构
  CREATE TABLE events (
      id INT,
      event_time TIMESTAMPTZ,
      original_tz TEXT
  );
  
  -- 插入数据时记录原始时区
  INSERT INTO events VALUES (1, '2020-01-01 23:00:00-07', '-07');
  
  -- 查询原始时区的日期
  SELECT (event_time AT TIME ZONE original_tz)::DATE AS local_date
  FROM events;
  

• 方案二：存储带时区的字符串（应急场景）
  如果无法修改表结构，可以将原始的带时区时间字符串存储为TEXT类型（而非timestamptz），查询时直接提取日期部分：

  SELECT SUBSTRING(event_time_str FROM 1 FOR 10) AS local_date
  FROM events;
  

  注意：这种方法会失去timestamptz类型的时间运算、索引等优势，仅适合临时应急。

如果你的业务场景中，时区信息可以通过其他逻辑推导（比如关联其他表的时区字段），也可以结合该逻辑使用AT TIME ZONE完成转换。

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