PostgreSQL如何通过查询结果动态设置会话时区？

场景与需求 #

我有存储group数据的表，每行包含带UTC时区的timestamp字段；另有building表存储建筑对应的时区信息。数据示例如下：

group表 #

building表 #

需要编写查询，按建筑所在时区返回指定group在某两日之间的数据。比如针对巴黎时区的建筑，传入本地时间2023-02-02T00:00时，需检索对应UTC时间2023-02-01T23:00:00开始的数据。

原方案问题 #

尝试通过SET TIMEZONE动态设置会话时区，但直接用子查询赋值会触发语法错误，原语句如下：

SET TIMEZONE to (SELECT time_zone AS tz
    FROM energy.building
    WHERE id = (SELECT building_id FROM energy."group" WHERE id = '06A20-026:1'));

SELECT *
FROM data."group"
WHERE id = '06A20-026:1' AND "timestamp" >= '2023-02-02' AND "timestamp" < '2023-02-03'                                
ORDER BY "timestamp" ASC

错误原因：SET TIMEZONE命令不支持直接使用子查询作为参数，必须传入字面量或通过变量动态赋值。

正确解法 #

解法1：查询内动态转换时间（推荐） #

无需修改会话时区，直接在WHERE子句中把传入的本地时间转换为UTC时间，关联建筑表获取对应时区：

SELECT g.*
FROM data."group" g
JOIN energy.building b ON g.building_id = b.id
WHERE g.id = '06A20-026:1'
  -- 将本地日期转为建筑时区时间，再转成UTC时间与表中字段比较
  AND g."timestamp" >= ('2023-02-02'::TIMESTAMP AT TIME ZONE b.time_zone) AT TIME ZONE 'UTC'
  AND g."timestamp" < ('2023-02-03'::TIMESTAMP AT TIME ZONE b.time_zone) AT TIME ZONE 'UTC'
ORDER BY g."timestamp" ASC;

解法2：动态设置会话时区（PL/pgSQL实现） #

如果必须修改会话时区，可通过PL/pgSQL的DO块先查询时区，再动态执行SET TIMEZONE命令：

DO $$
DECLARE
  target_timezone TEXT;
BEGIN
  -- 查询目标group所属建筑的时区
  SELECT time_zone INTO target_timezone
  FROM energy.building
  WHERE id = (SELECT building_id FROM energy."group" WHERE id = '06A20-026:1');
  
  -- 动态设置会话时区，用%L转义字符串避免SQL注入
  EXECUTE format('SET TIME ZONE %L', target_timezone);
END $$;

-- 此时会话时区已设置为目标时区，查询时将本地时间转UTC后过滤
SELECT *
FROM data."group"
WHERE id = '06A20-026:1' 
  AND "timestamp" >= ('2023-02-02'::TIMESTAMP) AT TIME ZONE 'UTC'
  AND "timestamp" < ('2023-02-03'::TIMESTAMP) AT TIME ZONE 'UTC'
ORDER BY "timestamp" ASC;

注意：此方法会修改当前会话的时区设置，后续查询都会受该时区影响，灵活性不如解法1。

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