按当地时间获取PostgreSQL表中各字段每日最大值

方法1：指定传感器字段（适合固定字段场景） #

直接使用UNNEST并行展开传感器名称和数值数组，同时完成时区转换与聚合：

SELECT
    DATE(datetime::timestamptz AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Berlin') AS berlin_date,
    sensor_name,
    MAX(sensor_value) AS daily_max_value
FROM
    sensor_data,
    UNNEST(ARRAY['s1', 's2', 's3']) AS sensor_name,
    UNNEST(ARRAY[s1, s2, s3]) AS sensor_value
WHERE
    sensor_value IS NOT NULL
GROUP BY
    berlin_date,
    sensor_name
ORDER BY
    berlin_date,
    sensor_name;

关键点说明 #

• 时区转换：datetime::timestamptz AT TIME ZONE 'UTC' 将存储的UTC时间转为带时区的时间戳，再通过AT TIME ZONE 'Europe/Berlin'转换为柏林当地时间，最后用DATE()提取日期作为分组维度。
• 宽转长：通过并行UNNEST将传感器名称和对应数值的数组逐行展开，避免创建中间表。
• 非空过滤：WHERE sensor_value IS NOT NULL确保只计算有效数据的最大值，MAX()函数也会自动忽略NULL，但显式过滤更清晰。

方法2：自动适配所有传感器字段（适合字段动态变化场景） #

利用jsonb函数自动提取所有非datetime的传感器字段，无需手动指定：

SELECT
    DATE(datetime::timestamptz AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Berlin') AS berlin_date,
    sensor_name,
    MAX((sensor_value)::numeric) AS daily_max_value
FROM
    sensor_data,
    jsonb_each_text(to_jsonb(sensor_data) - 'datetime') AS sensors(sensor_name, sensor_value)
WHERE
    sensor_value IS NOT NULL
GROUP BY
    berlin_date,
    sensor_name
ORDER BY
    berlin_date,
    sensor_name;

关键点说明 #

• 动态字段提取：to_jsonb(sensor_data) - 'datetime'将整行数据转为JSONB并排除datetime字段，jsonb_each_text将键值对展开为传感器名称和字符串值，再转为数值类型。
• 兼容性：无论后续新增多少s_i字段，该查询无需修改即可适配。

宽格式结果备选方案 #

如果接受宽格式输出，可直接按日期分组聚合每个传感器的最大值：

SELECT
    DATE(datetime::timestamptz AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Berlin') AS berlin_date,
    MAX(s1) AS s1_daily_max,
    MAX(s2) AS s2_daily_max,
    MAX(s3) AS s3_daily_max
FROM
    sensor_data
GROUP BY
    berlin_date
ORDER BY
    berlin_date;

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