DateTime difference from two tables
I am having 2 table by the name mt_upload and down_time and field are D开发者_运维百科ownTime and DownTime1... i need to caluculate the time difference between 2 field from 2 difference table.can anyone help me out
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.
$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
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>;
精彩评论