Performing aggregation through date and time in SQL

Question

I have a data-set which contains observations for several weeks with 2 minutes frequency. I want to increase the time interval from 2 minute to 5 minute. The problem is that, frequency of the observations are not always the same. I mean, theoretically, every 10 minute there should be 5 observation but usually it is not the case. Please let me know how I can aggregate the observations based on average function and with respect to the time and date of the observations. In other words aggregation based on every 5 minutes while number of observations are not the same for each 5 minute time interval. Moreover, I have date and time in timestamp format.

Example Data:

1 2007-09-14 22:56:12 5.39
2 2007-09-14 22:58:12 5.34
3 2007-09-14 23:00:12 5.16
4 2007-09-14 23:02:12 5.54
5 2007-09-14 23:04:12 5.30
6 2007-09-14 23:06:12 5.20

expected results:

1 2007-09-14 23:00 5.29
2 2007-09-14 23:05 5.34

Answer 1

The answers to this question likely provide good solutions to your problem, showing ways to efficiently aggregate data into time windows.

Essentially, use the avg aggregate with:

GROUP BY floor(extract(epoch from the_timestamp) / 60 / 5)