CODEWITHSUNDEEP
postgresqltimestampaggregate-functions
Marin Leontenko
Marin Leontenko

Reputation: 771

PostgreSQL calculate average values for parts of the day

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:

enter image description here

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.

Upvotes: 3

Views: 3134

Answers (2)

wildplasser
wildplasser

Reputation: 44250

If you also need the averages for intervals without any measurements, you'll need a calendar-table:

-- \i tmp.sql

CREATE TABLE the_temp(
        ztime timestamp primary key
        , ztemp double precision
        ) ;


INSERT INTO the_temp( ztemp, ztime )
VALUES (20, '2018-05-20 4:00')
    , (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')
        ;

        -- Generate calendar table
WITH cal AS(
        SELECT ts AS t_begin, ts+ '6hours'::interval AS t_end
        FROM generate_series('2018-05-20 0:00'::timestamp
                           , '2018-05-21 0:00', '6hours'::interval) ts
        )
SELECT cal.t_begin, cal.t_end
        , AVG( tt.ztemp)AS zmean
FROM cal
LEFT JOIN the_temp tt 
        ON tt.ztime >= cal.t_begin 
        AND tt.ztime < cal.t_end
GROUP BY cal.t_begin, cal.t_end
        ;

Upvotes: 2

klin
klin

Reputation: 121889

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)

Upvotes: 5

Related Questions