PostgreSQL时区问题排查：数据时间偏差与时区查询报错

1. 修复时区查询语法错误 #

你使用的是MySQL的时区查询语法，PostgreSQL不支持@@GLOBAL.time_zone这类写法，正确的查询方式如下：

• 查询当前会话时区：

  SHOW TIMEZONE;
  -- 或等价写法
  SELECT current_setting('timezone');
  

• 查询全局配置时区（postgresql.conf中默认设置的时区）：

  SELECT current_setting('log_timezone');
  

2. 解决hour字段匹配错误 #

问题核心是当前会话时区与hour字段存储的小时时区基准不匹配，导致查询逻辑偏离预期。

步骤1：确认并修正会话时区 #

如果你的本地时间是16:32，但执行SELECT EXTRACT(HOUR FROM NOW());返回15，说明会话时区被设置为UTC，需要切换到你的实际本地时区：

-- 替换为你的实际时区，例如中国时区用'Asia/Shanghai'，欧洲中部时间用'Europe/Paris'
SET TIMEZONE = 'Europe/Paris';

执行后再次查询SELECT EXTRACT(HOUR FROM NOW());，结果应与你的本地时间小时（16）一致。

步骤2：根据hour字段的时区基准调整查询 #

情况A：hour字段存储的是本地时区小时

直接使用本地时间小时匹配即可：

AND EXTRACT(HOUR FROM NOW()) = hour;

情况B：hour字段存储的是UTC时区小时

如果你需要按本地时间16点筛选数据，需将UTC时间的hour转换为本地时区小时后再匹配：

AND EXTRACT(HOUR FROM (MAKE_TIME(hour, 0, 0) AT TIME ZONE 'UTC') AT TIME ZONE current_setting('timezone')) = EXTRACT(HOUR FROM NOW());

步骤3：验证时间转换逻辑 #

执行以下语句确认时区转换是否符合预期：

SELECT 
  NOW() AS local_time,
  NOW() AT TIME ZONE 'UTC' AS utc_time,
  EXTRACT(HOUR FROM NOW()) AS local_hour,
  EXTRACT(HOUR FROM NOW() AT TIME ZONE 'UTC') AS utc_hour;

对比输出的local_hour（应等于16）和utc_hour（应等于15），就能明确之前查询匹配到hour=15数据的原因。

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