Already every day I puzzle how to make an hourly sample of the database, but with maximum and average values at each hour, besides, if possible, it would be worth displaying zero values at a specific hour (for beauty). The matter is aggravated by the fact that the version of PgSQL - 8.3
generate_series function does not work.
WITH also strangely refused to work as it is in the example. The
unnest similar - it does not work.
The last attempt was to combine an array and a table, for example through a
JOIN , to sample the entire array without exception and find all the entries from the tables by the specified time.
My table is as follows:
id | related | info | time |
It turns out the following query can select the total amount at a specific hour:
SELECT date_part('hour', tbl.time), COUNT( tbl.id ) FROM table AS tbl WHERE tbl.related = 'to something' tbl.info = 'info' GROUP BY date_part('hour', tbl.time) ORDER BY date_part('hour', tbl.time)
And you want to, really want to get the average at a specific hour. I suspect that everything should not be so complicated, but after a hint I climb to you.