SQL query that returns all dates not used in the table

Question

So let's say I have entries that look like this:

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

How can I build a query that will be returned on 2011-01-03 and 2011-01-05, i.e. unused dates. I am posting blogs to the future, and I need a request that will show me the days when I have not posted anything yet. It will look from the current date to two weeks in the future.

Update:

I'm not too worried about creating a permanent date table. Thinking about it, although it seems that the solution might be to create a small stored procedure that creates a temporary table. Sort of:

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 think it is simply impossible to choose the lack of data.

Answer 1

- SQL . ( ) .

, @BenHoffstein .

, . , integers i 0-13, 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;

Answer 2

( ). :

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');

, , :

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

, WHERE:

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

Answer 3

, , :

select '2011-01-03', count(*) from TABLE where postdate='2011-01-03' 
  having count(*)=0 union
select '2011-01-04', count(*) from TABLE where postdate='2011-01-04' 
  having count(*)=0 union
select '2011-01-05', count(*) from TABLE where postdate='2011-01-05' 
  having count(*)=0 union
... repeat for 2 weeks

2011 , ,

select a.days_2011
from all_days_2011
left join TABLE on a.days_2011=TABLE.postdate
where a.days_2011 between date(now()) and date(date_add(now(), interval 2 week))
and TABLE.postdate is null;

Answer 4

mysql SELECT. , , .

What I would do is fill out my blog table with all possible dates (for a year, and then repeat the process)

 create table blog (
    thedate date not null,
    thetext text null,
    primary key (thedate));

execution of a cycle to create all date records for 2011 (using a program, for example $ mydate is the date you want to insert)

  insert IGNORE into blog (thedate,thetext) values ($mydate, null);

(the IGNORE keyword does not generate an error (thedate is the primary key) if the date already exists).
Then you usually insert the values

  insert into blog (thedate,thetext) values ($mydate, "newtext") 
  on duplicate key update thetext="newtext";

Finally, to select blank entries, you just need to

  select thedate from blog where thetext is null;