Subtracting Dates with dates stored as DATETIME in mysql?

[coupe-r]

Hi All,

 

I need to subtract dates and display the number of days left.  I have a 'Start' date and an 'End' date in DATETIME format in the DB.  Not quite sure where to start.  A simply start - end doesn't work  .

 

Start = 2011-11-01-00:00:00

End = 2011-11-30-23:59:59

 

Since it is now 2011-11-27, my output should equal 3.  Any help is appreciated.

[coupe-r]

After playing around,  I have the following which gives me '03' as my output, which is correct...

 

$finalTime = strtotime($row['end']) - mktime();
echo date("d", $finalTime);

 

Is this the correct way of doing it?

[teynon]

I'm sure there is a better way.

 

<?php
    function dateDiff($date1, $date2) {
        $return = array();
        // Format: YYYY-MM-DD-HH:MM:SS
        $date[0] = mktime(substr($date1, 11, 2), substr($date1, 14, 2), substr($date1, 17,2), substr($date1, 5, 2), substr($date1, 8, 2), substr($date1, 0, 4));
        $date[1] = mktime(substr($date2, 11, 2), substr($date2, 14, 2), substr($date2, 17,2), substr($date2, 5, 2), substr($date2, 8, 2), substr($date2, 0, 4));
        
        $difference = $date[1] - $date[0];
        $return["date1"] = $date[0];
        $return["date2"] = $date[1];
        $return["days"] = floor($difference / 60 / 60 / 24);
        $return["hours"] = floor($difference / 60 / 60) - ($return["days"] * 24);
        $return["minutes"] = floor($difference / 60) - ($return["days"] * 24 * 60) - ($return["hours"] * 60);
        $return["seconds"] = $difference - ($return["days"] * 24 * 60 * 60) - ($return["hours"] * 60 * 60) - ($return["minutes"] * 60);
        
        return $return;
    }
    $date1 = "2011-11-01-00:00:00";
    $date2 = "2011-11-30-23:59:59";
    
    $dates = dateDiff($date1, $date2);
    
    print_r($dates);
    

 

Outputs: Array ( [date1] => 1320120000 [date2] => 1322715599 [days] => 30 [hours] => 0 [minutes] => 59 [seconds] => 59 )

[teynon]

The initial format you posted wasn't in a format that strtotime accepts. If you have a format that it accepts, then use strtotime instead of the mktime I did.

[coupe-r]

Thanks Teynon...

 

I really need a function like that to get the difference in days?

 

Here is my data:

 

echo strtotime($row['end']);
echo strtotime("now");

$finalTime = strtotime($row['end']) - strtotime("now");

echo $finalTime;

$left = date("d", $finalTime);

 

My output is as follow:

 

Original $row['end'] = 2012-11-30 23:59:59

 

strtotime($row['end']) == 1354337999

strtotime("now"); == 1322453719

 

$finalTime; == 31884280

 

$left = 04

 

 

My $left variable should be over 365 days.  All I need to do is convert $finalTime into DAYS, which is what $finalTime = strtotime($row['end']) - strtotime("now"); should do, no?

[Pikachu2000]

There's no need to do any of the calculations in php at all, really.

 

SELECT DATEDIFF( end, start )

[coupe-r]

Hi Pikachu,

 

I didn't know DATEDIFF was a function, however, I don't need the difference between Start and End, I need the different between today and end.  This would tell me how many days are left in the lease.

 

Thanks.

 

But DATEDIFF(l.end, CURDATE()) AS dateDiff works great!!! thanks again!!