Postgres generate_series return the series timestamp, even when no data-CodePudding

I have a database that has metrics inserted every 3 minutes. I'm trying to generate a time series to report aggregate values in 10 minute intervals, to use for displaying on a graph. The idea here is to return an hours worth of data, with the aggregate values combined into the 10 minute intervals.

I have some code that handles this well already:

SELECT
    width_bucket(
        created::timestamp,
        array( select generate_series( timestamp '2022-11-19 19:00:00 00', '2022-11-19 20:00:00 00', '10 minutes') )
        ) as partition,
            worker, AVG(hashrate) AS hashrate, AVG(sharespersecond) AS sharespersecond, count(*)
            FROM minerstats
            WHERE created >= '2022-11-19 19:00:00 00' AND created <= '2022-11-19 20:00:00 00'
            GROUP BY 1, 2, worker
            ORDER BY 1, 2, worker

This works and it returns the partition in numeric form so 2022-11-19 19:10:00 00 through 2022-11-19 19:20:00 00 would be 2 .. but would be ideal if i could get it to return 2022-11-19 19:10:00 00 or 2022-11-19 19:20:00 00 even if there is no data for that series.

Can anybody point me in the right direction or help with how I could do this to return the series when there's no data, along with the timestamp of the series?

db fiddle: https://dbfiddle.uk/5RZxReoE

CodePudding user response：

Using Postgres 13-(no date_bin):

SELECT
    ten_min,
    worker,
    AVG(hashrate) AS hashrate,
    AVG(sharespersecond) AS sharespersecond,
    count(*)
FROM (
    SELECT
        generate_series(timestamptz '2022-11-19 19:00:00 00', '2022-11-19 19:50:00 00', '10 minutes') AS ten_min) AS t
    LEFT JOIN minerstats AS ms ON created <@ tstzrange(t.ten_min, ten_min   '0:10'::time)
GROUP BY
    ten_min,
    worker
ORDER BY
    ten_min,
    worker;

  ten_min         |   worker   |     hashrate      |   sharespersecond    | count 
------------------------- ------------ ------------------- ---------------------- -------
 11/19/2022 11:00:00 PST | NULL       |              NULL |                 NULL |     1
 11/19/2022 11:10:00 PST | testbench  |     776206061.875 | 0.030125000000000002 |     8
 11/19/2022 11:10:00 PST | testbench2 |       842887331.5 |               0.0325 |     2
 11/19/2022 11:20:00 PST | testbench  | 718040215.3333334 |  0.02788888888888889 |     9
 11/19/2022 11:20:00 PST | testbench2 |         687194767 |                0.027 |     1
 11/19/2022 11:30:00 PST | testbench  |      932726496.75 | 0.036125000000000004 |     8
 11/19/2022 11:30:00 PST | testbench2 |         719753390 |                0.028 |     2
 11/19/2022 11:40:00 PST | testbench  | 612414545.6666666 | 0.023777777777777773 |     9
 11/19/2022 11:40:00 PST | testbench2 |         700852403 |                0.027 |     1
 11/19/2022 11:50:00 PST | testbench  |     483093391.375 | 0.018875000000000003 |     8
 11/19/2022 11:50:00 PST | testbench2 |       505780476.5 |               0.0195 |     2

UPDATE corrected

ON t.ten_min <@ tstzrange(created, created '0:10'::time)

ON created <@ tstzrange(t.ten_min, ten_min '0:10'::time)

Using date_bin from Postgres 14 :

 SELECT
    ten_min,
    worker,
    AVG(hashrate) AS hashrate,
    AVG(sharespersecond) AS sharespersecond,
    count(*)
FROM (
    SELECT
        generate_series(timestamptz '2022-11-19 19:00:00 00', '2022-11-19 19:50:00 00', '10 minutes') AS ten_min) AS t
    LEFT JOIN minerstats AS ms ON t.ten_min = date_bin ('10 minutes', created, '2001-01-01')
GROUP BY
    ten_min, worker
ORDER BY
  ten_min, worker;
         ten_min         |   worker   |     hashrate      |   sharespersecond    | count 
------------------------- ------------ ------------------- ---------------------- -------
 11/19/2022 11:00:00 PST | NULL       |              NULL |                 NULL |     1
 11/19/2022 11:10:00 PST | testbench  |     776206061.875 |             0.030125 |     8
 11/19/2022 11:10:00 PST | testbench2 |       842887331.5 |               0.0325 |     2
 11/19/2022 11:20:00 PST | testbench  | 718040215.3333334 |  0.02788888888888889 |     9
 11/19/2022 11:20:00 PST | testbench2 |         687194767 |                0.027 |     1
 11/19/2022 11:30:00 PST | testbench  |      932726496.75 | 0.036125000000000004 |     8
 11/19/2022 11:30:00 PST | testbench2 |         719753390 |                0.028 |     2
 11/19/2022 11:40:00 PST | testbench  | 612414545.6666666 | 0.023777777777777773 |     9
 11/19/2022 11:40:00 PST | testbench2 |         700852403 |                0.027 |     1
 11/19/2022 11:50:00 PST | testbench  |     483093391.375 | 0.018875000000000003 |     8
 11/19/2022 11:50:00 PST | testbench2 |       505780476.5 |               0.0195 |     2