How to Use generate_series to Avoid Gaps In Data

SQL Tutorial Home

Any series of contiguous values can have gaps in the middle. We can have a series of hours, days, or just simple integer values. If we have gaps in this series and we want to fill with artificial data, we can use the generate_series() Postgres function to generate the complete series, then using a left join we can match our data with the complete series generated with generate_series(). Let’s see an example.

Any series of contiguous values can have empty spaces in the middle. Whether it is hours, days, or just simple integer values, if we have empty spaces and want to fill in with artificial data, we can use the Postgres generate_series() function to generate the complete series. Then using a left join we can match our data with the complete series generated with generate_series(). Let’s look at an example.

Suppose we have a shop with the daily revenue in a table, for every day we have one row with the daily revenue, however for those days when we do not open or we didn’t sell any product, we don’t have records in the table. Suppose for the week from July 11 to July 17 we have these data

select * from daily_revenue
day revenue
2021-07-12 2300
2021-07-14 2110
2021-07-15 1930
2021-07-17 1850

And we want a report with all the days in the week from 7/11 to 7/17, then the simple query:

select * 
from generate_series('11/07/2021'::timestamp,'17/07/2021'::timestamp,'1 day'::interval ) day

Return all the days in the period 7/11/21 - 7/17/21 as we can see: day

day
2021-07-12 00:00:00
2021-07-13 00:00:00
2021-07-14 00:00:00
2021-07-15 00:00:00
2021-07-16 00:00:00
2021-07-17 00:00:00

Then we can do a left join and obtain all days in the week, with revenue

select d.day, coalesce(d_r.revenue,0) revenue
from (select day 
      from generate_series('07/11/2021'::timestamp,
                           '07/17/2021'::timestamp, 
                           '1 day'::interval) day
	) d
left join daily_revenue d_r ON d.day = d_r.day
day revenue
2021-07-11 0
2021-07-12 2300
2021-07-13 0
2021-07-14 2110
2021-07-15 1930
2021-07-16 0
2021-07-17 1850

IN THIS PAGE