Postgress sum on specific field and Group BY hour and objectid

Question

I don't have to much experience with POSTGRESS SQL I have datatable with the following fields:

id  timestamp            value   sensor
1   2020-10-10 14:10:18   1.4     1
2   2020-10-10 14:40:15   1.1     1
3   2020-10-10 15:40:12   0.1     2
4   2020-10-10 15:50:01   2.0     2
5   2020-10-11 15:50:01   1.2     3

I want to get a result per sensor with sum {value} per hour like:

timestamp            value   sensor
2020-10-10 14:00:00   2.5     1
2020-10-10 15:00:00   2.1     2
2020-10-11 15:00:01   1.2     3

I tried this:

SELECT sensor, timestamp, sum(value) FROM datatable ORDER BY timestamp  GROUP BY date_part('hour', timestamp),sensor

it rise me error "timestamp" must appear in the GROUP BY clause or be used in an aggregate function

How I should build this query?

Answer 1

you can try to use date_trunc function, in addition, you might use GROUP BY before then ORDER BY

date_trunc(field, source [, time_zone ])

SELECT date_trunc('hour', "timestamp"),sum(value) ,sensor
FROM datatable 
GROUP BY date_trunc('hour', "timestamp"),sensor
ORDER BY 1 

sqlfiddle