插入数据后timestamptz字段时区丢失问题求助

问题原因 #

1. PostgreSQL timestamptz的存储特性：timestamp with time zone（简称timestamptz）的设计逻辑是将输入的带时区时间统一转换为UTC存储，以此避免多时区场景下的存储混乱。查询时若未指定时区，数据库默认会返回UTC格式的时间。
2. Hasura的默认行为：Hasura GraphQL Engine默认以UTC作为timestamptz字段的输出时区，不会自动将存储的UTC时间转换回你输入时的时区。

解决办法 #

1. 统一设置PostgreSQL容器的时区 #

在Docker Compose的postgres服务配置中添加时区环境变量，让数据库以指定时区处理时间输出：

postgres:
  image: postgres
  ports:
    - "5432:5432"
  volumes:
  - db_data:/var/lib/postgresql/data
  environment:
    POSTGRES_PASSWORD: postgressecretkey
    TZ: "America/Los_Angeles"  # 替换为你需要的时区，比如Asia/Shanghai
    POSTGRES_INITDB_ARGS: "--timezone=America/Los_Angeles"

重启容器后，PostgreSQL会将timestamptz字段以指定时区返回，Hasura查询结果也会同步使用这个时区。

2. 同步设置Hasura的时区 #

在Hasura服务的环境变量中添加时区配置，确保和PostgreSQL时区一致：

graphql-engine:
  image: hasura/graphql-engine:v2.10.1
  ports:
    - "8080:8080"
  depends_on:
    - "postgres"
  restart: always
  environment:
    HASURA_GRAPHQL_DATABASE_URL: postgres://postgres:postgressecretkey@postgres:5432/postgres
    HASURA_GRAPHQL_ENABLE_CONSOLE: "true"
    HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log
    TZ: "America/Los_Angeles"  # 和PostgreSQL时区保持一致

这样Hasura在处理时间字段时会遵循指定时区，返回的时间会带上对应时区偏移。

3. 查询时手动转换时区 #

如果不想修改容器配置，可以在GraphQL查询中使用PostgreSQL的AT TIME ZONE函数直接转换：

mutation MyMutation {
  insert_attendance(objects: {in: "2022-09-29T17:00:00.000-07:00"}) {
    returning {
      in_local: in(at_time_zone: "America/Los_Angeles")
    }
  }
}

返回的in_local字段就是转换为指定时区的时间。

4. 自定义SQL函数实现全局转换 #

如果需要全局统一处理timestamptz的输出，可以创建PostgreSQL函数，然后在Hasura中配置为计算字段：
首先创建SQL函数：

CREATE OR REPLACE FUNCTION convert_to_local_time(t timestamptz)
RETURNS text AS $$
SELECT t AT TIME ZONE 'America/Los_Angeles';  # 替换为目标时区
$$ LANGUAGE sql STABLE;

然后在Hasura控制台中，给attendance表添加一个计算字段，调用这个函数，后续查询时直接使用该计算字段即可得到指定时区的时间。

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