How to select rows by time interval in PostgreSQL?

Question

I have a table with timestamp field and usage 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 sum of usage for a period of '1 week' with '30 minutes' interval. I can get data only for intervals in 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 ... .

If I get you right - you want to sum(usage) over a field that is between date_trunc('minute', timestamp) and date_trunc('minute', timestamp) + '30 minutes'::interval as a subquery, and then the upper one?.. right?.. — Vao Tsun
– Vao Tsun, Commented Jun 18, 2015 at 8:23
I think that's right. I want each result's row show usage of 30 minutes. — Foad Tahmasebi
– Foad Tahmasebi, Commented Jun 18, 2015 at 8:29

Matt · Accepted Answer · 2015-06-18 08:30:14Z

12

Use the following to get what your 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)

answered Jun 18, 2015 at 8:30

Matt

15.1k28 gold badges109 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Foad Tahmasebi Over a year ago

return error in line 1: "column "timestamp" must appear in the GROUP BY clause or be used in an aggregate function"

Matt Over a year ago

it is in the group by, did you copy all the code, or did you add to it?

Foad Tahmasebi Over a year ago

I use pgadmin to test this query and it draws error line under 'timestamp' in this section on line one! "round((extract('epoch' FROM timestamp)" . and about changes, I change 'timestamp' to 'time_stamp' and 'my_table' to 'records', just this!

Foad Tahmasebi Over a year ago

I test this code without any change again. but the error is still. I change group by section to 'group by clock', but query return all of records.

Foad Tahmasebi Over a year ago

Finally, I found problem :). you should bring out second 1800 from 'round' function and group by 'clock'. please update your answer, I want check it as a correct answer. thank you.

Collectives™ on Stack Overflow

How to select rows by time interval in PostgreSQL?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related