terungwa Posted January 11, 2014 Share Posted January 11, 2014 I am trying to track the time of resolution of complaints in my application, so i have tried to get the difference between the time a problem was reported and date it was resolved as shown in code below. I have noticed that calculating the difference between the time expressions uses only the date parts of the values. so 5-01-2014 minus 27-12-2013 returns -2 days instead of 9 days. I have also noticed that the MySQL DATEDIFF() Function uses only the date parts of the values in the calculation too. How may I get the correct duration returned as days? Thanks. <?php require_once('./includes/connection.inc.php'); $conn = dbConnect('read'); $sql = 'SELECT date_format(reported, "%d-%m-%Y") AS date_reported, date_format(Date_Resolved, "%d-%m-%Y") AS date_resolved FROM complaints ORDER BY created DESC'; $result = $conn->query($sql) or die(mysqli_error()); $numRows = $result->num_rows; ?> <!DOCTYPE HTML> <html> <head> <meta charset="utf-8"> <title>Customer Complaints</title> </head> <body> <table> <thead> <tr> <th>Resolution Period</th> </tr> </thead> <tbody> <?php while ($row = $result->fetch_assoc()) { $date1=$row["date_reported"]; $date2=$row["date_resolved"]; ?> <tr> <td> <?php echo $date2-$date1; ?> days</td> </tr> <?php } ?> </tbody> </table> </body> </html> Quote Link to comment Share on other sites More sharing options...
ginerjm Posted January 11, 2014 Share Posted January 11, 2014 Try adding this to your query: SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate you just have to ensure that your dates are in the correct format (this appears to be yyyy-mm-dd) Quote Link to comment Share on other sites More sharing options...
Barand Posted January 11, 2014 Share Posted January 11, 2014 or $date_reported = '2013-12-27 10:30:00'; $date_resolved = '2014-01-05 15:00:00'; $d1 = new DateTime($date_resolved); $d2 = new DateTime($date_reported); echo $d1->diff($d2)->format('%d days %h hrs %i mins'); //-> 9 days 4 hrs 30 mins Quote Link to comment Share on other sites More sharing options...
terungwa Posted January 11, 2014 Author Share Posted January 11, 2014 (edited) or $date_reported = '2013-12-27 10:30:00'; $date_resolved = '2014-01-05 15:00:00'; $d1 = new DateTime($date_resolved); $d2 = new DateTime($date_reported); echo $d1->diff($d2)->format('%d days %h hrs %i mins'); //-> 9 days 4 hrs 30 mins this solution gave me strange output: i got 0 days 4 hrs 30 mins instead of 30 days 4 hrs 30 mins from code below after changing dates. $date_reported='2013-12-27 10:30:00'; $date_resolved='2014-01-27 15:00:00'; $d1 = new DateTime($date_resolved); $d2 = new DateTime($date_reported); echo $d1->diff($d2)->format('%d days %h hrs %i mins'); //-> 0 days 4 hrs 30 mins Edited January 11, 2014 by terungwa Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted January 11, 2014 Solution Share Posted January 11, 2014 That's because it is 1 mth 0 days 4hrs try echo $d1->diff($d2)->format('%m mths %d days %h hrs %i mins'); //-> 1 mth 0 days 4 hrs 30 mins or echo $d1->diff($d2)->format('%a days %h hrs %i mins'); //-> 31 days 4 hrs 30 mins Quote Link to comment Share on other sites More sharing options...
terungwa Posted January 12, 2014 Author Share Posted January 12, 2014 gratias, this has resolved my challenge. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.