postgresql,select输出结果+1小时

您只需将这两列添加到 group by 以下内容:

SELECT CONCAT(TO_CHAR(timestamp::timestamp, 'YYYY-MM-DD HH'), ':00:00') as time_from,
       CONCAT(TO_CHAR(timestamp::timestamp + interval '1 hour', 'YYYY-MM-DD HH'), ':00:00') as time_to,
       car_id,
       SUM(km) as driven 
FROM car_data
GROUP BY time_from, time_to, car_id 
ORDER BY time_from, car_id;

但是,我不会将时间戳转换为字符串。相反,使用 date_trunc() 以下内容:

SELECT date_trunc('hour', timestamp::timestamp) as time_from,
       date_trunc('hour', timestamp::timestamp) + interval '1 hour' as time_to,
       car_id,
       SUM(km) as driven 
FROM car_data
GROUP BY date_trunc('hour', timestamp::timestamp), car_id 
ORDER BY time_from, car_id;

一种选择是将当前工作查询放入CTE,然后使用它生成所需的输出:

WITH cte AS (
    SELECT
        timestamp::timestamp AS time_from,
        car_id,
        SUM(km) AS driven
    FROM car_data
    GROUP BY timestamp::timestamp, car_id
)

SELECT
    CONCAT(TO_CHAR(time_from, 'YYYY-MM-DD HH'), ':00:00') AS time_from,
    CONCAT(TO_CHAR(time_from + interval '1 hour', 'YYYY-MM-DD HH'), ':00:00') AS time_to,
    car_id,
    driven
FROM cte
ORDER BY
    time_from, car_id;