CODEWITHSUNDEEP
postgresqldatetime
DudeNukem
DudeNukem

Reputation: 47

Monthly-hourly-average calculate from Postgresql database

I have the time and the values in the data base. I need to calculate for a given month the average during each hour i.e.

YYYY-mm-dd (the day can be omitted)
2021-01-01 00:00:00 value=avg(values from 00:00:00 until 00:59:59 for every day of this month at this hour interval) 
2021-01-01 01:00:00 value=avg(values from 01:00:00 until 01:59:59 idem as above)
...
2021-01-01 23:00:00 value=avg(values from 23:00:00 until 23:59:59)
2021-02-01 00:00:00 value=avg(values from 00:00:00 until 00:59:59)
2021-02-01 01:00:00 value=avg(values from 01:00:00 until 01:59:59)
...
2021-02-01 23:00:00 value=avg(values from 23:00:00 until 23:59:59)
...

Upvotes: 0

Views: 1246

Answers (1)

O. Jones
O. Jones

Reputation: 108651

You can use date_trunc('hour', datestamp) in a GROUP BY statement, something like this.

SELECT DATE_TRUNC('hour', datestamp) hour_beginning, AVG(value) average_value
  FROM mytable
 WHERE datestamp >= '2021-01-01'
   AND datestamp <  '2021-02-01'
 GROUP BY DATE_TRUNC('hour', datestamp)
 ORDER BY DATE_TRUNC('hour', datestamp)

To generalize, in place of DATE_TRUNC you can use any injective function.

You could use

to_char(datestamp, 'YYYY-MM-01 HH24:00:00')

to get one result row per hour for every month in your date range.

SELECT to_char(datestamp, 'YYYY-MM-01 HH24:00:00') hour, 
       AVG(value) average_value
  FROM mytable
 GROUP BY to_char(datestamp, 'YYYY-MM-01 HH24:00:00')
 ORDER BY to_char(datestamp, 'YYYY-MM-01 HH24:00:00')

Upvotes: 2

Related Questions