Filling in empty dates

user_13063400 0
Viewed 42

This query returns the number of alarms created by day between a specific date range.

SELECT CAST(created_at AS DATE) AS date, SUM(1) AS count
FROM ew_alarms
LEFT JOIN site ON site.id = ew_alarms.site_id
AND ew_alarms.created_at BETWEEN '12/22/2020' AND '01/22/2021' AND (CAST(EXTRACT(HOUR FROM ew_alarms.created_at) AS INT) BETWEEN 0 AND 23.99)
GROUP BY CAST(created_at AS DATE)
ORDER BY date DESC

Result: screenshot

What the best way to fill in the missing dates (1/16, 1/17, 1/18, etc)? Due to no alarms created on those days these results throw off the daily average I'm ultimately trying to achieve.

Would it be a generate_series query?

postgresql sql
1 Answers

Yes, use generate_series(). I would suggest:

SELECT gs.date, COUNT(s.site_id) AS count
FROM GENERATE_SERIES('2020-12-22'::date, '2021-01-22'::date, INTERVAL '1 DAY') gs(dte) LEFT JOIN
     ew_alarms a
     ON ew.created_at >= gs.dte AND
        ew.created_at < gs.dte + INTERVAL '1 DAY' LEFT JOIN
     site s
     ON s.id = a.site_id 
GROUP BY gs.dte
ORDER BY date DESC;

I don't know what the hour comparison is supposed to be doing. The hour is always going to be between 0 and 23, so I removed that logic.

Note: Presumably, you want to count something from either site or ew_alarms. That is expected with LEFT JOINs so 0 can be returned.

user_11440350
Related