sql - PostgreSQL，从+1小时选择输出结果

我有以下SQL功能，适用于GROUP BY小时：

SELECT
CONCAT(TO_CHAR(timestamp::timestamp, 'YYYY-MM-DD HH'), ':00:00') as time_from,
car_id,
SUM(km) as driven from car_data
group by TO_CHAR(timestamp::timestamp, 'YYYY-MM-DD HH'), car_id
order by time_from, car_id;

我想要一个额外的输出，它是time_from+1小时间隔。
我试过：

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 TO_CHAR(timestamp::timestamp, 'YYYY-MM-DD HH'), car_id
order by time_from, car_id;

但那报告我还需要GROUP BY这个字段：

ERROR:  column "car_data.time" must appear in the GROUP BY clause or be used in an aggregate function

，我不想这么做。我怎样才能最好地存档？

最佳答案

您只需将这两列添加到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;

关于sql - PostgreSQL，从+1小时选择输出结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51153081/