Calculating time between DATETIME with php

Question

I am calling some data from a simple SELECT query where the row contains three fields, start_time(DATETIME), end_time(DATETIME) & reopen_time(DATETIME).

I am trying to work out the time between both the start_time and end_time & also between the start_time & reopen_time if this is a case. The way I am going to determine is by another column called complete, which sets a value of either 1 or 0. It will calculate the start_time and end_time IF the complete value is 1 and start_time and reopen_time is value is 0.

Now, my PHP looks like the following so far; I am looking for somebody to help by casing and eye:

$id=$_GET['id']; //this grabs the id from the previous page depending on which task is clicked.
$sql = "SELECT * FROM to_do_list WHERE id=$id";
$result = mysqli_query($db, $sql);

if(mysqli_num_rows($result) > 0){
    while($row = mysqli_fetch_assoc($result)){    
        $date1 = $row["start_time"];        
        $date2 = $row["end_time"];            
        $diff = abs(strtotime($date2) - strtotime($date1));            
        $years = floor($diff / (365*60*60*24));
        $months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24));
        $days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));            
        printf("%d years, %d months, %d days\n", $years, $months, $days);            
    }
} else {
    echo "There are no tasks!";
}

Now, the result I get back is: 0 years, 0 months, 0 days but there isn't an error. I am presuming the format of the data from the database is maybe incorrect?

Answer 1

I would take some time (see the pun?) an look at the PHP Class DateTime (http://php.net/manual/en/class.datetime.php).

This class already has a built in method for taking the difference between dates including cases like leap years, creating a DataInterval Object. Then using the DataInterval object you can format and pullout the pieces you want (Months, days, years, etc...)

while($row = mysqli_fetch_assoc($result)){    
    $date1 = $row["start_time"];        
    $date2 = $row["end_time"];    
    $datetime1 = new DateTime($date1);
    $datetime2 = new DateTime($date2);
    $interval = $datetime1->diff($datetime2);
    echo $interval->format('%y years, %m months, %d days');     
 }

Note: if the $date1 and $date2 fields are exactly same, you'll get and interval that reports 0 for months, years, etc... as there's no difference in time.

Also, be aware, because DateInterval takes into account variations like leap years, you'll need to remember not every 'month' is equal. If you need to know exact days difference, DateInterval provides a public property days:

 $date1 = new DateTime('2015-02-27');  // 2015 was a 'regular' year, Feb had 28 days
 $date2 = new DateTime('2015-03-27');
 $interval_1_2 = $date1->diff($date2);

 $date3 = new DateTime('2016-02-27'); // 2016 was a 'leap' year, Feb had 29 days
 $date4 = new DateTime('2016-03-27');
 $interval_3_4 = $date3->diff($date4);

 echo $interval_1_2->format('%m months, %d day '); // 1 month 0 days 
 echo "Total Days: ".$interval_1_2->days."\n"; // 28 days

 echo $interval_3_4->format('%m months, %d day');  // 1 month 0 days
 echo "Total Days: ".$interval_3_4->days."\n"; // 29 days

First rule of Dev-club: Don't re-invent the wheel unless:

1. You don't care about functionality and are just experimenting with problems solved, many, many, times before for a some kind of learning experiment.
2. and willing to probably end up with a quirky result that doesn't quite cover 100% use cases.
3. Just having fun.

Answer 2

You could actually do this directly in mysql using TIMESTAMPDIFF

$id= filter_input(INPUT_GET, 'id',FILTER_VALIDATE_INT); 
//crude sql injection filter, dont use in production

$sql = "
    SELECT *,
    TIMESTAMPDIFF(YEAR, start_time , end_time) as years,
    TIMESTAMPDIFF(MONTH, start_time , end_time) as months,
    TIMESTAMPDIFF(DAY, start_time , end_time) as days
    FROM to_do_list WHERE id=$id
";

$result = mysqli_query($db, $sql);

if(mysqli_num_rows($result) > 0){
    while($row = mysqli_fetch_assoc($result)){    
        $date1 = $row["start_time"];        
        $date2 = $row["end_time"];            
        $years = $row["years"];        
        $months = $row["months"];        
        $days = $row["days"];        

        printf("%d years, %d months, %d days\n", $years, $months, $days);            
    }
} else {
    echo "There are no tasks!";
}

see this answer for info on how to properly handle sql injection prevention