How to find missing rows (dates) in mysql table?

Question

I tried several topics like this: How to find missing rows of data using SQL? here, but I could not get it to work in my situation.

I have a table with a name postsin MySQL in which I save user diaries every day. Sometimes users forget to write a message during the day, and I want them to be able to send it later. So db structures look like this:

date           userid
2011-10-01     1
2011-10-02     1
(missing)
2011-10-04     1
2011-10-05     1
(missing)
2011-10-07     1

So, I want to show a drop-down list of missing dates in this table of missing lines for the user so that he can select the date on which he wants to send a message.

How can i do this? Thanks.

Answer 1

, . , .

DROP PROCEDURE IF EXISTS FillDateTable;

delimiter //
CREATE PROCEDURE FillDateTable()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  drop table if exists datetable;
  create table datetable (thedate datetime primary key, isweekday smallint);

  SET @x := date('2000-01-01');
  REPEAT 
    insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;
    SET @x := date_add(@x, interval 1 day);
    UNTIL @x >= '2030-12-31' END REPEAT;
END//
delimiter ;

CALL FillDateTable;

LEFT JOIN

SELECT thedate
FROM datetable
LEFT JOIN posts on posts.date = datetable.thedate
WHERE posts.date IS NULL

, "" 2000 2030 . MIN MAX ( ), ..

SELECT thedate
FROM datetable
INNER JOIN (select min(date) postStart, max(date) postEnd
            FROM posts
            where userid=123) p on datetable.thedate BETWEEN p.postStart and p.postEnd
LEFT JOIN posts on posts.date = datetable.thedate
WHERE posts.date IS NULL

Answer 2

- . PostgreSQL; - .

. 2011 , - . ( ).

select c.cal_date
from calendar c
left join posts p on (c.cal_date = p.date)
where p.date is null
  and c.cal_date between '2011-10-01' and '2011-10-31'
  and p.userid = 1
order by c.cal_date

Answer 3

( ) , , . , , , .

This should not be a space problem, and not if they write more than they missed. For example, if they write for 4 days and miss 1.

In addition, you would run the script and delete entries with null names, null content, and a date older than X days. If they have not added a missing post in X days, they will probably never do so.

Sorry if my solution is trivial / too abstract.