Calculate the difference between two dates

Question

I use PHP and MySQL and want to calculate the time difference between two dates. I have a message table, there is one field in this createdate table. I want to know the difference in day and time from the current date in the format 1 day 2 hours ago . What is the best way to do this?

Answer 1

Use PHP's built-in date functions:

 <?php $start_time = "Ymd H:i:s"; // fill this in with actual time in this format $end_time = "Ymd H:i:s"; // fill this in with actual time in this format // both of the above formats are the same as what MySQL stores its // DATETIMEs in $start = new DateTime($start_time); $interval = $start->diff(new DateTime($end_time)); echo $interval->format("d \d\a\y\sh \h\o\u\r\s"); 

• DateInterval Documentation
• DateTime :: diff documentation

Answer 2

 SELECT TIMESTAMPDIFF(HOUR,createdate,NOW()) as diff_in_hours FROM table1; 

Then on the php side you can easily convert diff_in_hours value to days + hours format.

Answer 3

In MySQL, you can use the functions DATEDIFF() and TIMEDIFF() .

 SELECT DATEDIFF(CURDATE(), createdate) AS output_day, TIMEDIFF(CURDATE(), createdate) AS output_time FROM message_table 

For output_day, it is already in the day block. But output_time requires additional manipulation to get the hourly portion of the time difference.

Hope this helps.