截断带时区时间戳时如何保留时区信息？

核心问题原因 #

date_trunc处理timestamptz时，本身返回的仍是带时区信息的timestamptz类型，但出现时区丢失的情况通常是两个原因：

• 未在截断时明确指定用户时区，默认使用了服务器时区进行计算，导致结果的时区关联不符合预期；
• 传递给PHP时，未正确保留时区标识（比如直接将timestamptz转为无时区的字符串，或PHP驱动未正确识别时区元数据）。

规范实现方案 #

1. 正确截断并保留时区关联（PostgreSQL 12+）

PostgreSQL 12及以上版本支持date_trunc的第三个参数直接指定目标时区，无需额外转换即可得到该时区下的截断结果，且保留timestamptz类型的时区信息：

-- 示例：将UTC时间截断为Australia/Adelaide时区的小时级时间
SELECT 
  date_trunc('hour', '1999-12-31 20:59:59+00:00'::timestamptz, 'Australia/Adelaide') AS truncated_ts,
  'Australia/Adelaide' AS user_timezone;

返回结果的truncated_ts是带+10:30偏移的timestamptz类型，同时通过user_timezone字段明确时区标识。

2. 兼容低版本PostgreSQL的写法

如果使用低于12的版本，可先将timestamptz转换为目标时区的timestamp（无时区），截断后再转换回timestamptz：

SELECT 
  date_trunc('hour', '1999-12-31 20:59:59+00:00'::timestamptz AT TIME ZONE 'Australia/Adelaide') 
  AT TIME ZONE 'Australia/Adelaide' AS truncated_ts,
  'Australia/Adelaide' AS user_timezone;

3. 带时区的格式化输出

如果需要直接输出如2000-01-01 07:00:00 +10:30格式的字符串，使用to_char指定时区格式：

SELECT 
  to_char(
    date_trunc('hour', '1999-12-31 20:59:59+00:00'::timestamptz, 'Australia/Adelaide'),
    'YYYY-MM-DD HH24:MI:SS TZ'
  ) AS formatted_time_with_tz;

4. 多时区分组统计示例

针对按用户时区分组统计的场景，将截断后的时间和时区作为分组依据：

-- 假设表events有event_time timestamptz字段，按用户时区Asia/Tokyo的小时维度统计
SELECT 
  date_trunc('hour', event_time, 'Asia/Tokyo') AS hour_period,
  'Asia/Tokyo' AS user_timezone,
  COUNT(*) AS event_count
FROM events
GROUP BY hour_period, user_timezone
ORDER BY hour_period;

PHP端时区处理 #

从数据库获取结果后，需结合返回的truncated_ts（timestamptz类型）和user_timezone字段，构建带正确时区的DateTime对象：

// 从查询结果中读取数据
$truncatedTs = $row['truncated_ts'];
$timezone = $row['user_timezone'];

// 构建带时区的DateTime实例
$date = new DateTime($truncatedTs, new DateTimeZone($timezone));
// 输出指定格式的时间（含时区偏移）
echo $date->format('Y-m-d H:i:s P');

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