SQL : built-in way to detect gaps in a arbitrary series of dates?

Question

I have a set of tables that feed into a historical reports. It's taking a while to generate the report now, and I want to create cache tables that will store the compiled data that then feed into the report.

One key of these cache tables will be a date field. I want to make sure that I don't have any gaps in any series of dates that I use -- e.g. I don't want to have records for the 1st and 3rd of January while the 2nd has no row.

I can't think of a way to enforce this with any of the relational tools in an RDBMS. I could set up a table of dates to make sure that related records don't fall outside a series of dates -- to disallow the 2nd of Jan. if it had no matching row in my 'date series' table -- but I don't see how to automagically ensure that I don't skip dates in a range.

Is there an automatic way to do this in the database schema definition? Or will I have to check for gaps in my code?

I'm using MySQL, but this seems RDBMS-agnostic.

score 2 · Accepted Answer · answered Feb 10 '12 at 18:32

I don't know of a built in way to do it, but the way I've handled this myself is to generate a table of dates. I have a static table called tbDates, and when I need to report on a range of dates and don't want to skip dates for which i might be missing data for, I do:

select *
from 
    tbDates d
    left join myTable t
        on d.date = t.date

Depending on what format and datatype your date column is in your tabl you might need to format the data little so it can join against tbDates. Some of my apps don't have tbDates. In those cases it's trivial to generate a temp table with continuous dates for the range I want.

score 1 · Answer 2 · answered Feb 10 '12 at 18:29

This is a great example of the usefulness of a numbers table (http://www.projectdmx.com/tsql/tblnumbers.aspx).

Create your numbers table with, I dunno, ten thousand rows. You can then select from that table and use the DATEADD function.

SELECT DATEADD(d, a.id, '2012-01-01')
FROM NumbersTable

With that query in place, it's as simple as a good LEFT JOIN to your data, and you can see what dates are missing.

Alternatively, something I like to do, rather than use a numbers table, is to create a Dates table. In fact, you can populate a dates table with nothing but (non-holiday) weekdays. Easy!

Such date tables are incredibly useful for this sort of thing. I typically have several columns in the table for denoting things like which days are weekdays, holidays, etc. — Rich.Carpenter, Feb 10 '12 at 19:30

score 0 · Answer 3 · answered Feb 10 '12 at 18:29

There is no way to do this automatically, but you could generate the dates and store them in a temporary table.

That could be done in a stored procedure:

create temporary table
insert values from start date to end date (each day)
select your results and join with the temporary table
select only rows that don't have corresponded in the temporary table

SQL : built-in way to detect gaps in a arbitrary series of dates?

3 Answers3