getting missing dates

[jasmeet]

i have dates in mysql database :

 

 

03/09/2013

05/09/2013

06/09/2013

09/09/2013

these dates stored in database.

 

i am not getting how to get missing dates from the table???

 

 

 

[jasmeet]

i have dates in mysql database :

 

 

03/09/2013

05/09/2013

06/09/2013

09/09/2013

these dates stored in database.

 

i am not getting how to get missing dates from the table???

query used id select * from tablename where date between current date and "????" but i need to find missing dates ... ??/

i am not getting what comes in ????

Edited September 9, 2013 by jasmeet

[vinny42]

You cannot select things that are not in your table.

Your application will have to generate the missing dates.

[Barand]

i have dates in mysql database :

 

 

03/09/2013

05/09/2013

06/09/2013

09/09/2013

these dates stored in database.

 

 

You shouldn't have those dates in a database table, you should have these dates in DATE type fields (format yyyy-mm-dd):

 

2013-09-03

2013-09-05

2013-09-06

2013-09-09

 

Query the dates in the table ordered by date (which you can do if you use yyyy-mm-dd) and as you read each date compare with the previous date. If the date difference is greater that one day you have missing dates which can then be calculated.

[vinny42]

 

and as you read each date compare with the previous date.

 

Or do a self-join to get the full set of 'dates that have no match for the day before' at once. :-)

[Barand]

Or do a self-join to get the full set of 'dates that have no match for the day before' at once. :-)

Would work for single-date gaps but wouldn't give all missing dates for larger gaps.

 

This would

$db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE );

$sql = "SELECT thedate FROM dates";

$res = $db->query($sql);

// first date record
$row = $res->fetch_row();
$prevdate = new DateTime($row[0]);
echo $prevdate->format('Y-m-d').'<br>';

// rest of dates in table
while ($row = $res->fetch_row()) {
    $dt = new DateTime($row[0]);
    if ($dt->diff($prevdate)->days > 1) {
        while ($prevdate->modify('+1 day') < $dt) {
            echo '<strong>' . $prevdate->format('Y-m-d').' -- missing</strong><br>';
        } 
    }
    echo $dt->format('Y-m-d')."<br>";
    $prevdate = $dt;
}

[vinny42]

 

Would work for single-date gaps but wouldn't give all missing dates for larger gaps.

 

I meant that it would give a series of gaps, saving the trouble of having to scan the dates that don't have gaps. Let the database do what it's good at.

 

In PostgreSQL ofcourse you'd use generate_series() to "fill the gaps" and get a list of dates straight from the database.