sql script to group hourly using datepart and fill the empty with 0

Question

This script gives the hourly count of the timestamps in the table.

SELECT date_trunc('hour', s.fill_instant) h , count(date_trunc('hour', s.fill_instant)) c FROM sms s
left join station s2 on
s.station_id = s2.station_id
where
s2.address like '%arizona%' and s.fill_date between '2021-09-19' and '2021-09-19'
GROUP BY date_trunc('hour', s.fill_instant)
order by date_trunc('hour', s.fill_instant) asc;

in the table like the following

2021-09-19 00:00:00 3
2021-09-19 02:00:00 20
2021-09-19 03:00:00 6
2021-09-19 13:00:00 7
2021-09-19 14:00:00 11
2021-09-19 15:00:00 6

How can I insert the Zeros in the non present hours. so it shows the map of complete 24 hours. much like this

2021-09-19 00:00:00 3
2021-09-19 01:00:00 0
2021-09-19 02:00:00 20
2021-09-19 03:00:00 6
2021-09-19 04:00:00 0
2021-09-19 05:00:00 0
2021-09-19 06:00:00 0
2021-09-19 07:00:00 0
2021-09-19 08:00:00 0
2021-09-19 09:00:00 0
2021-09-19 10:00:00 0
2021-09-19 11:00:00 0
2021-09-19 12:00:00 0
2021-09-19 13:00:00 7
2021-09-19 14:00:00 11
2021-09-19 15:00:00 6
2021-09-19 16:00:00 0
2021-09-19 17:00:00 0
2021-09-19 18:00:00 0
2021-09-19 19:00:00 0
2021-09-19 20:00:00 0
2021-09-19 21:00:00 0
2021-09-19 22:00:00 0
2021-09-19 23:00:00 0
2021-09-19 24:00:00 0

score 1 · Answer 1 · answered Oct 20 '21 at 20:36

Use generate_series to get all hours for the given day as a CTE then left join it with your query:

WITH hours as (Select * from generate_series('2021-09-19 00:00:00'::timestamp, '2021-09-19 23:59:59'::timestamp, INTERVAL '1 hour') as hr)
SELECT hours.hr, coalesce(qry.c, 0) as c
FROM hours 
LEFT JOIN(SELECT date_trunc('hour', s.fill_instant) h , count(date_trunc('hour', s.fill_instant)) c FROM sms s
left join station s2 on s.station_id = s2.station_id
where
s2.address like '%arizona%' and s.fill_date between '2021-09-19' and '2021-09-19'
GROUP BY date_trunc('hour', s.fill_instant)
order by date_trunc('hour', s.fill_instant) asc) qry on qry.h = hours.hr

This ran without a problem with JPA environment native query — Naveen Gopalakrishna, Dec 28 '21 at 04:57

Stefanov.sm · Answer 2 · 2021-10-20T20:58:54.857

1

A more or less generic pattern for filling gaps in a sequence would be this:
Use your existing query (t) and outer join it with the dense sequence of hours (ds). Use coalesce to set null values of t.c as 0.

with t as 
(
 .. your query here .. 
)
select ds.h, coalesce(t.c, 0) c
from generate_series
(
  timestamp '2021-09-19T00:00:00', 
  timestamp '2021-09-19T24:00:00', 
  interval '1 hour'
) as ds(h)  
left outer join t on t.h = ds.h;

edited Oct 20 '21 at 20:58

answered Oct 20 '21 at 20:47

Stefanov.sm

11,215
2
21
21

Is there a way to make it dynamic means the goal is to write this query in JPA . Can we replace the timestamp 'yyyy-mm-ddTHH:MM:ss' with a variable – Naveen Gopalakrishna Oct 21 '21 at 18:19
There surely is - by replacing `yyyy-mm-ddTHH:MM:ss` literal constants with parameters. Some details in [this](https://stackoverflow.com/questions/66071165/how-to-pass-parameters-in-a-native-query-jpa?noredirect=1&lq=1) SO thread and [here](https://www.baeldung.com/jpa-query-parameters). – Stefanov.sm Oct 21 '21 at 18:25

score 0 · Answer 3 · answered Oct 20 '21 at 21:22

Another way to generate all hours of a day is use a recursive query.

Then you have to make a LEFT JOIN between hours subquery and your query, and in those rows that do not match your query (c column is null), you have to put a zero (I used a CASE statement).

WITH RECURSIVE hours AS (SELECT '2021-09-19 00:00:00'::timestamp AS hour
                         
                         UNION ALL
                         
                         SELECT hour + interval '1 hour'
                         FROM hours
                         WHERE hour < '2021-09-19 23:00:00')
                         
SELECT hours.hour, CASE WHEN sq.c IS NULL THEN 0 ELSE sq.c END AS c
FROM hours
LEFT JOIN (SELECT date_trunc('hour', s.fill_instant) h, count(date_trunc('hour', s.fill_instant)) c 
           FROM sms s
           LEFT JOIN station s2 ON s.station_id = s2.station_id
           WHERE s2.address like '%arizona%' AND s.fill_date = '2021-09-19'
           GROUP BY date_trunc('hour', s.fill_instant)) AS sq ON hours.hour = sq.h
ORDER BY hours.hour;

sql script to group hourly using datepart and fill the empty with 0

3 Answers3