Menu
I have the following simple table:
ID TIMESTAMP VALUE
4 2011-05-27 15:50:04 1253
5 2011-05-27 15:55:02 1304
6 2011-05-27 16:00:02 1322
7 2011-05-27 16:05:01 1364
I would like to average the VALUES, and GROUP each TIMESTAMP day into 6 hourly buckets. e.g 00:00 to 06:00, 06:00 to 12:00, 12:00 to 18:00 & 18:00 to 00:00.
I am able to group by year, month, day & hour using the following query:
select avg(VALUE),
EXTRACT(year from TIMESTAMP) AS year,
EXTRACT(month from TIMESTAMP) AS month,
EXTRACT(day from TIMESTAMP) as day
from TABLE
group by year,month,day
But I am unable to group each day into 4 periods as defined above, any help is most welcome.
382
How to Group by Timein PostgreSQL When you want to group by minute, hour, day, week, etc., it's tempting to just group by your timestamp column, however, then you'll get one group per second, which is likely not what you want. Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc.
Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc. The PostgreSQL function you need here is date_trunc.
You can use this for PostgreSQL. 600 is 10 minutes in seconds. The idea is to convert timestamp to epoch, divide by interval desired in minutes then round to get the desired interval
When you want to group by minute, hour, day, week, etc., it's tempting to just group by your timestamp column, however, then you'll get one group per second, which is likely not what you want. Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc.
I think grouping the integer value of the quotient of the (Hour of your timestamp / 6) should help. Try it and see if it helps. Your group by should be something like
group by year, month, day, trunc(EXTRACT(hour from TIMESTAMP) / 6)
The logic behind this is that when the hour part of the date is divided by 6, the int values can only be
0 - 0:00 - 5:59:59
1 - 6:00 - 11:59:59
2 - 12:00 - 17:59:59
3 - 18:00 - 23:59:59
Grouping using this should put your data into 4 groups per day, which is what you need.
174
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
