DateTime difference from two tables

Question

Hi,I am having 2 table by the name mt_upload and down_time and field are DownTime and DownTime1... i need to caluculate the time difference between 2 field from 2 difference table.can anyone help me out

Answer 1

$date1 = '...'; // fetch this from your first table
$date2 = '...'; // fetch this from your second table
// if the dates are NOT unix timestamps use PHP's strtotime() or MySQL's UNIX_TIMESTAMP() to convert them

$difference = abs($date1 - $date2); // difference in second
// divide by 60 for minutes, 3600 for hours, etc etc

Answer 2

In MySql:

select timediff(t2.DownTime,t1.DownTime1) 
from mt_upload t1, down_time t2 
where t1.id=<some_id> and t2.id=<some_id>;

(of course you need some IDs to select the right records)

This returns you a string in the form HOURS:MINUTES:SECONDS

If you want number of seconds, you can do this:

select hour(timediff(t2.DownTime,t1.DownTime1))*3600
+minute(timediff(t2.DownTime,t1.DownTime1))*60
+second(timediff(t2.DownTime,t1.DownTime1))
from mt_upload t1, down_time t2 
where t1.id=<some_id> and t2.id=<some_id>;

Answer 3

When using PHP 5.3:

For getting the difference in a useable form so it can be shown to the user fill the data into DateTime objects as in $date1 = new DateTime('2009-11-09 12:13:14'); and then use Datetime::diff() to get a DateInterval object.

Doing this is better than manually calculating the differences as manually handling daylight saving time switches, leap seconds and similar date things can be really hard.