Find missing dates in php

Question

Hi I have two textboxes for date from and to. When I select dates in both textboxes, representing a time interval, I'd like to fill in missing dates, so I could query some records our in table along with date suppose i have

or may be another date please help me

<?php
$from='2014-08-11'; 
$to='2014-09-10';

$query="SELECT * FROM stats WHERE Feeddate BETWEEN '2014-08-11' AND '2014-09-10'"; 
?>

I don't quite understand though. Suppose you have `2014-08-11` and `2014-09-10`. What do you mean by find missing dates? — Wesley Brian Lachenal, Aug 12 '14 at 05:50
all records related to date in our table when i execute the query suppose i have 2014-08-02,2014-08-08 two records related to this date in table when i execute query missing date will be 2014-08-03,2014-08-04,2014-08-05,2014-08-06,2014-08-07 — Craftstrail, Aug 12 '14 at 05:53
yes between from which i came to know exactly which date i have forget to update or add records i our software — Craftstrail, Aug 12 '14 at 05:56
From what I understood you'd like to generate a list of dates between, i.e. `2014-08-03` and `2014-08-05`m which would be `2014-08-03`, `2014-08-04`, `2014-08-05`. Am I correct in my assumption? — Havelock, Aug 12 '14 at 05:57

Havenard · Answer 1 · 2014-08-12T06:00:19.463

$from   = '2014-08-11'; 
$to     = '2014-09-10';
$query  = "SELECT Feeddate FROM stats WHERE Feeddate BETWEEN '$from' AND '$to' ORDER BY Feeddate";

$cursor = DateTime::createFromFormat('Y-m-d', $from);
$res    = mysql_query($query);

while ($row = mysql_fetch_assoc($res))
{
    $date = DateTime::createFromFormat('Y-m-d', $row['Feeddate']);
    while ($cursor != $date)
    {
        echo "Day is missing: " . $cursor->format('Y-m-d') . "<br>";
        $cursor->modify('+1 day');
    }
    $cursor->modify('+1 day');

}

score 1 · Accepted Answer · edited May 23 '17 at 12:11

Didn't test it, but something like this. You'll want to create a temp table of the series of dates in the range you are looking for. For example 2014-08-11 to 2014-09-10. This has a way of doing that:

Generating a series of dates

Then you'll want to query against that

SELECT *
FROM x
(
     SELECT date 
     FROM dateseries
     WHERE date BETWEEN '2014-08-11' AND '2014-09-10'
) as x
WHERE NOT IN ( 
    SELECT date 
    FROM stats
    WHERE Feeddate BETWEEN '2014-08-11' AND '2014-09-10'
);

Hope you get the idea. Just know that MySql sucks balls at subquery optimization, so you might want to move that subquery to a join.

Find missing dates in php

2 Answers2