query data in dates collection

Question

I have a table like this

id	timestamps	value
1	2022-01-01 00:00:00	2
2	2022-01-01 00:10:00	2
3	2022-01-01 00:20:00	1
4	2022-01-01 00:30:00	7
5	2022-01-01 00:40:00	4
6	2022-01-01 00:50:00	1
7	2022-01-01 01:00:00	3
8	2022-01-01 01:10:00	9
9	2022-01-01 01:20:00	5
10	2022-01-01 01:30:00	8
11	2022-01-01 01:40:00	0
12	2022-01-01 01:50:00	1

the data is being inserted every 10 minutes. now I want to select averge data every 1 hour in a 3 days range. currently, combined with PHP there is 72 queries (3 x 24hr) that being run every http request is coming from the client. the queries took about 6s, which is not a good idea. so is there any other approach that can decrease the execution time down to several ms?

Answer 1

To resolve your issue, I did the following (all of the code below is available on the fiddle here):

Firstly, I created a table as follows:

CREATE TABLE reading
(
  r_id INT         NOT NULL GENERATED ALWAYS AS IDENTITY, 
  r_ts TIMESTAMPTZ NOT NULL,                              -- always tz!
  r_val INT        -- NULL             -- may be NULL - missed readings?
);

I try to give my fields meaningful names - i.e. r_id instead of just id and r_ts (I use the _ts suffixes for TIMESTAMP(TZ)s - also, I always use TIMESTAMPTZ unless there's a good reason not to (which is the exception rather than the rule).

You may wish to consider the possibility of missing readings (NULL) for r_val. At the bottom of the fiddle, I've included the 3 possible behaviours of the AVG() function (all 3 values present - simple average, 1 out of a set of 3 values missing - average over the two valid readings, 3 missing out of 3, average is NULL).

I used your data to populate the table (see fiddle).

Then, we create a calendar tables - you may or may not wish to have this as part of your permanent schema (here's how to create one for SQL Server). It won't take up very much room and may tidy your SQL?

WITH calendar AS
(
  SELECT 
    t
  FROM
    GENERATE_SERIES
    (
      '2021-12-31 21:00:00'::TIMESTAMPTZ,
      '2022-01-01 02:00:00',   
      '1 HOUR'
    ) AS tab(t)
)
SELECT * FROM calendar;

I've only included 3 hours before midnight (when your data starts) and 1 hour after your data finishes - you may have data for timestamps before and after.

Result:

t
2021-12-31 21:00:00+00
2021-12-31 22:00:00+00
2021-12-31 23:00:00+00
2022-01-01 00:00:00+00
2022-01-01 01:00:00+00
2022-01-01 02:00:00+00
6 rows

So, we have every hour between 2021-12-31 21:00:00 and 2022-01-01 02:00:00 - inclusive.

Then, we can do the following:

WITH calendar AS
(
  SELECT 
    t
  FROM
    GENERATE_SERIES
    (
      '2021-12-31 21:00:00'::TIMESTAMPTZ,
      '2022-01-01 02:00:00',   
      '1 HOUR'
    ) AS tab(t)
)
SELECT
  DATE(c.t) AS dt_cal,
  EXTRACT(HOUR FROM c.t) AS hr_cal,
  ROUND(AVG(r_val), 2)
FROM
  calendar c
LEFT JOIN reading r
  ON   
    EXTRACT(HOUR FROM c.t) = EXTRACT(HOUR FROM r.r_ts)
GROUP BY   dt_cal, EXTRACT(HOUR FROM c.t)
ORDER BY dt_cal, hr_cal;

Result:

dt_cal     hr_cal  round
2021-12-31     21   NULL
2021-12-31     22   NULL
2021-12-31     23   NULL
2022-01-01      0   2.83
2022-01-01      1   4.33
2022-01-01      2   NULL
6 rows

So, we have our averages over the hours for which data is available.

If you arrive into work and want to check the last 3 days, you can do the following:

WITH calendar AS
(
  SELECT 
    t
  FROM
    GENERATE_SERIES
    (
      NOW()::DATE - INTERVAL '3 DAYS',
      NOW()::DATE - INTERVAL '1 HOUR',
      '1 HOUR'
    ) AS tab(t)
)
SELECT 
  t  -- whatever you want here - i.e. the query from above!
FROM calendar;

Result - see fiddle - 72 hour slots = 3 days x 24 hours (= 72).

So, you have every 1 HOUR TIMESTAMP from 3 days ago at 00:00:00 up to 23:00:00 last night.

I also played around with using AVG() as a window function - see the fiddle - can also obtain cumulative averages. These may even be more performant - again, see the EXPLAIN (ANALYZE, BUFFERS, VERBOSE) in the fiddle, but this depends on your own data and on what indexes you want to use.