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.

Answer 1

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.