Announcing our $3.4M seed round 🚀 Read more →

How to Avoid Gaps in Data in Redshift

If you're grouping by time and you don't want gaps in your report data, you need to generate a series of time values and use it to do an outer join with your data. Redshift does not offer any special function for this, because the recommended way for data warehouses is to have a time dimension table that has all the possible time values.

Still if you need to generate a series of time values, you can do so by selecting from an arbitrary big table - one that has at least the number of rows as the number of values you want to generate:

-- This generates 60 days, the latest being the current date
SELECT (getdate()::date - row_number() OVER (ORDER BY true))::date AS day
FROM orders

Ready for a modern SQL editor?