Question 89457 in PostgreSQL Question posted in
The official documentation can be found here.

SQL: select the average value of a column per hour per day for the last 30 days – Postgresql

I have a table like below which contains temperature data for the last 6 months. It takes about 30-40 readings of temperature in an hour.
‘time’ is in epoch milliseconds.

time temperature
1678270897857 25
1678270777712 30
1678270657548 38
1678270537402 27
1678270417258 38
… …

I need a query in PostgreSQL to get the ‘temperature’ averaged per hour per day for the last 30 days (2023-01-15 till 2023-02-15).

So the expected result should look something like:

date average_temp
2023-01-15 01:00:00 28.5
2023-01-15 02:00:00 32.6
2023-01-15 03:00:00 21.9
… …
2023-01-15 23:00:00 33.8
2023-01-16 00:00:00 26.1
2023-01-16 01:00:00 29.4
… …
2023-02-15 22:00:00 31.3
2023-02-15 23:00:00 22.7
Tags:

2

Answers


  1. 0

    You could try to use below query.

    1. Generate time series from start date to end date by using generate_series function, timeframe per each record is 1 hour.
    2. Join this time series to your data, then calculate average temperature per each timeframe.
    WITH date_range AS 
      (SELECT '2023-02-09 00:00:00'::timestamp AS start_date
            , '2023-03-09 23:59:59'::timestamp AS end_date),
     time_series AS
      (SELECT  EXTRACT(EPOCH FROM t) * 1000 AS start_time, (EXTRACT(EPOCH FROM t) + 3600) * 1000 AS end_time
       FROM date_range 
            CROSS JOIN generate_series(start_date, end_date, interval '1 hour') t)
       
SELECT TO_TIMESTAMP(t.end_time/1000) AS date, AVG(d.temperature) AS average_temperature
FROM time_series t
     LEFT JOIN <your table> d ON d.time BETWEEN t.start_time AND t.end_time
GROUP BY t.start_time, t.end_time

    See demo here

    Login or Signup to reply.
  2. 0

    You could convert the times to timestamps using to_timestamp then use date_trunc to ’round’ to hour.

    CREATE TABLE temp_table(time bigint, temperature INT);

INSERT INTO temp_table(time, temperature)
VALUES (1678270897857,25),
(1678270777712,30),
(1678270657548,38),
(1678270537402,27),
(1678270417258,38);

SELECT date_trunc('hour',to_timestamp(time/1000)) AS date, AVG(temperature) AS avg_temp
FROM temp_table
GROUP BY date
ORDER BY date;
    date avg_temp
    2023-03-08 10:00:00+00 31.6000000000000000
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search