Calculate difference between two datetimes
10,206
Solution 1
Use PHP's built in date functions:
<?php
$start_time = "Y-m-d H:i:s"; // fill this in with actual time in this format
$end_time = "Y-m-d 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\s h \h\o\u\r\s");
Solution 2
SELECT TIMESTAMPDIFF(HOUR,createdate,NOW()) as diff_in_hours FROM table1;
Then on php side you can easily convert the value of diff_in_hours
to days + hours format.
Solution 3
You can use DATEDIFF()
and TIMEDIFF()
functions in MySQL.
SELECT DATEDIFF(CURDATE(), createdate) AS output_day,
TIMEDIFF(CURDATE(), createdate) AS output_time
FROM message_table
For output_day it is already in day unit. But output_time require additional manipulation to get the hour part of the time difference.
Hope this helps.
Related videos on Youtube
Author by
Sanjay Khatri
I am web-developer http://www.sanjaykhatri.in http://en.gravatar.com/sanjaykhattri
Updated on May 28, 2022Comments
-
Sanjay Khatri almost 2 years
I am using PHP and MySQL, and want to calculate date time difference between two datetimes. I have a message table, in that table
createdate
is one field. I want to find out day and time difference from current date in the format1 day 2 hours ago
. What is the best way to go about this? -
Osh Mansor about 13 years@Sanjay: The solution I wrote gives the time difference up to a fraction of a sec in the following format
HH:MM:ss.s
-
a1ex07 about 13 years@Sanjay: The first argument of
TIMESTAMPDIFF
can also beMINUTE
,orSECOND
, orMICROSECOND
(dev.mysql.com/doc/refman/5.5/en/…). In your question you asked about days+hours, so I putHOUR
unit to my sample code. -
Vipul Hadiya over 7 yearsWhat about <= PHP 5.2 ?