mysql - 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

One solution would be to create a separate table with one column to hold all dates from now until eternity (or whenever you expect to stop blogging). For example:

CREATE TABLE Dates (dt DATE);
INSERT INTO Dates VALUES ('2011-01-01');
INSERT INTO Dates VALUES ('2011-01-02');
...etc...
INSERT INTO Dates VALUES ('2099-12-31');

Once this reference table is set up, you can simply outer join to determine the unused dates like so:

SELECT d.dt 
FROM Dates d LEFT JOIN Blogs b ON d.dt = b.dt 
WHERE b.dt IS NULL

If you want to limit the search to two weeks in the future, you could add this to the WHERE clause:

AND d.dt BETWEEN NOW() AND ADDDATE(NOW(), INTERVAL 14 DAY)