Elixir结合PostgreSQL生成时间序列并转换UTC至指定时区的问题

问题分析 #

你原代码的核心问题有两个：

1. 错误使用now()作为时间序列的起止，而非给定的sd和ed；
2. 时区转换逻辑混乱，导致to_char无法正确识别并输出目标时区标识。

PostgreSQL中AT TIME ZONE的关键逻辑：

• timestamp with time zone（如你的sd/ed）执行AT TIME ZONE '时区'，会得到该时区下的timestamp without time zone本地时间；
• timestamp without time zone执行AT TIME ZONE '时区'，会转换为关联该时区的timestamp with time zone格式，此时to_char才能正确提取时区信息。

修正后的代码 #

sd = ~U[2024-07-31 05:00:00.000000Z]
ed = ~U[2024-08-02 04:59:59.000999Z]
timezone = "US/Central"

from(
  c in fragment(
    """
    SELECT generate_series(
      ? AT TIME ZONE ?,
      ? AT TIME ZONE ?,
      '-1 hour'
    ) AS entry
    """,
    ^sd, ^timezone,
    ^ed, ^timezone
  ),
  select: %{
    date_str: fragment(
      -- 用TZ显示时区缩写（CDT/CST），用TZR显示完整时区名称（US/Central）
      "to_char(? AT TIME ZONE ?, 'YYYY-MM-DD HH24:MI:SS TZ')",
      c.entry, ^timezone
    )
  }
)

代码说明 #

1. 生成正确的时间序列：
   将UTC起始/结束时间sd/ed转换为US/Central时区的本地时间（无时区格式），以此作为generate_series的参数，确保生成的序列是目标时区下按小时间隔的时间点。
2. 正确格式化时区：
   把生成的无时区本地时间重新关联US/Central时区，再用to_char的TZ（或TZR）格式符输出时区标识，最终得到符合预期的带时区时间字符串。

示例输出 #

使用TZ格式符会得到类似：
2024-07-30 23:00:00 CDT

使用TZR格式符会得到：
2024-07-30 23:00:00 US/Central

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