How to select rows by time interval in PostgreSQL?
I have a table with a time field and a use field like this
timestamp | usage
'2015-06-13 13:45:58' | 240
'2015-06-13 13:45:59' | 480
'2015-06-13 13:46:00' | 240
'2015-06-13 13:46:01' | 320
...
I want to get the usage amount for a period of "1 week" with an interval of "30 minutes". I can only get data for intervals of minute, hour, day and ...
SELECT date_trunc('minute', timestamp) as clock, sum(usage)
FROM my_table
WHERE timestamp > localtimestamp - INTERVAL '1 week'
GROUP BY clock
how to get data for intervals like "5 minutes", "30 minutes", "2 days" and ....
+3
source to share
1 answer
Use the following to get what came after.
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + INTERVAL '1 second' * round((extract('epoch' FROM timestamp) / 1800) * 1800) AS clock, SUM(usage)
FROM my_table
WHERE timestamp > localtimestamp - INTERVAL '1 week'
GROUP BY round(extract('epoch' FROM timestamp) / 1800)
+2
source to share