PostgreSQL calculate average values for parts of the day

Question

I have a postgres table with measured temperatures and timestamp of measurement. Measuring interval is 30 minutes, but sometimes it skips, so I don't get the same number of measurements each day.

The table looks like this:

I need to create a view that shows average temperature for each day divided into four 6 hour intervals: 00-06, 06-12, 12-18 and 18-24. It should look something like this:

avg_temp, time

|24.5 | 2018-05-13 00:00:00 |

|22.1 | 2018-05-13 06:00:00 |

|25.6 | 2018-05-13 12:00:00 |

|20.6 | 2018-05-13 18:00:00 |

|21.8 | 2018-05-14 00:00:00 |

etc. etc.

Answer 1

You can round timestamps to quarters of a day with the following expression (on an exemplary data):

with my_table(temp, time) as (
values
    (20, '2018-05-20 4:00'::timestamp),
    (21, '2018-05-20 5:00'),
    (22, '2018-05-20 6:00'),
    (23, '2018-05-20 7:00'),
    (24, '2018-05-20 12:00'),
    (25, '2018-05-20 19:00')
)
select avg(temp), time::date + (extract(hour from time)::int/ 6* 6)* '1h'::interval as time
from my_table
group by 2
order by 2

         avg         |        time         
---------------------+---------------------
 20.5000000000000000 | 2018-05-20 00:00:00
 22.5000000000000000 | 2018-05-20 06:00:00
 24.0000000000000000 | 2018-05-20 12:00:00
 25.0000000000000000 | 2018-05-20 18:00:00
(4 rows)