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> Link to comment https://forums.phpfreaks.com/topic/285289-date-difference-calculation/ 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) Link to comment https://forums.phpfreaks.com/topic/285289-date-difference-calculation/#findComment-1464847 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 Link to comment https://forums.phpfreaks.com/topic/285289-date-difference-calculation/#findComment-1464872 Share on other sites More sharing options...
terungwa Posted January 11, 2014 Author 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 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 Link to comment https://forums.phpfreaks.com/topic/285289-date-difference-calculation/#findComment-1464877 Share on other sites More sharing options...
Barand Posted January 11, 2014 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 Link to comment https://forums.phpfreaks.com/topic/285289-date-difference-calculation/#findComment-1464878 Share on other sites More sharing options...
terungwa Posted January 12, 2014 Author Share Posted January 12, 2014 gratias, this has resolved my challenge. Link to comment https://forums.phpfreaks.com/topic/285289-date-difference-calculation/#findComment-1464899 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.