SQL query that returns all dates not used in a table

Question

So lets say I have some records that look like:

2011-01-01 Cat
2011-01-02 Dog
2011-01-04 Horse
2011-01-06 Lion

How can I construct a query that will return 2011-01-03 and 2011-01-05, ie the unused dates. I postdate blogs into the future and I want a query that will show me the days I don't have anything posted yet. It would look from the current date to 2 weeks into the future.

Update:

I am not too excited about building a permanent table of dates. After thinking about it though it seems like the solution might be to make a small stored procedure that creates a temp table. Something like:

CREATE PROCEDURE MISSING_DATES()
BEGIN
    CREATE TABLE TEMPORARY DATES (FUTURE DATETIME NULL)
    INSERT INTO DATES (FUTURE) VALUES (CURDATE())
    INSERT INTO DATES (FUTURE) VALUES (ADDDATE(CURDATE(), INTERVAL 1 DAY))
    ...
    INSERT INTO DATES (FUTURE) VALUES (ADDDATE(CURDATE(), INTERVAL 14 DAY))

    SELECT FUTURE FROM DATES WHERE FUTURE NOT IN (SELECT POSTDATE FROM POSTS)

    DROP TABLE TEMPORARY DATES
END 

I guess it just isn't possible to select the absence of data.

Answer 1

You're right — SQL does not make it easy to identify missing data. The usual technique is to join your sequence (with gaps) against a complete sequence, and select those elements in the latter sequence without a corresponding partner in your data.

So, @BenHoffstein's suggestion to maintain a permanent date table is a good one.

Short of that, you can dynamically create that date range with an integers table. Assuming the integers table has a column i with numbers at least 0 – 13, and that your table has its date column named datestamp:

   SELECT candidate_date AS missing
     FROM (SELECT CURRENT_DATE + INTERVAL i DAY AS candidate_date
             FROM integers
            WHERE i < 14) AS next_two_weeks
LEFT JOIN my_table ON candidate_date = datestamp
    WHERE datestamp is NULL;