How to calculate datetime difference between two datetimes

[xiaawan]

Hi All,
How can we calculate the difference between two dates including time. For example begin datetime is "2007-10-04 11:26:00" and end dateTime is "2007-10-04 11:50:00". What can be the best solution here. Thanks in Advance.

[felgall]

Since datetimes are stored internally as milliseconds since midnight 1st Jan 1970, subtracting one from the other will give the difference in milliseconds. Simply divide the result by the appropriate number to give the units that you require the answer in.

[sweatje]

Since datetimes are stored internally as milliseconds since midnight 1st Jan 1970

Actually, just plain seconds:

 $ php -r 'echo gmdate("r",0), "\n", gmdate("r",60), "\n", gmdate("r",1000);'
Thu, 01 Jan 1970 00:00:00 +0000
Thu, 01 Jan 1970 00:01:00 +0000
Thu, 01 Jan 1970 00:16:40 +0000

Also, it looks like lots of other people have come up with date diff solutions. A quick look through some of those sites should hopefully get you on your way.

[catweasel]

Hi All,
How can we calculate the difference between two dates including time. For example begin datetime is "2007-10-04 11:26:00" and end dateTime is "2007-10-04 11:50:00". What can be the best solution here. Thanks in Advance.

Just do it in a mysql query -

SELECT DATEDIFF('2007-10-04 11:26:00','2007-10-04 11:50:00');

outputs 0 days.

SELECT TIMEDIFF('2007-10-04 11:26:00','2007-10-04 11:50:00');

outputs -00:24:00

[xiaawan]

thanks all of you guys,
Searching on the web, I got the following solution that gives me the best solution
http://www.gidnetwork.com/b-16.html

[logic_earth]

Thats not the best solution.

[xiaawan]

Thats not the best solution.

I agree this is not the best solution for everyone. But I required the difference in hours, minutes and seconds between two dates so it helps me, but you are right there may be some other solution helpful for everyone. So I am in the search process, if I get a better one, surely I will go for that , but at the moment it helps me.

[catweasel]

Where are these dates coming from? is it user input or are they coming from a database?
What is the context of this problem?

[xiaawan]

Where are these dates coming from? is it user input or are they coming from a database?
What is the context of this problem?

Yes, these values are coming from Database,

Actually there are three columns StartDate (DateTime), EndDate (DateTime) and Length (Time), and I have to calculate the difference in time between two dates. That was my actual problem. I was having difficulties to calculate the time difference as I was unable to find any built in function that would do the job for me so searching on net I found this page http://www.gidnetwork.com/b-16.html. So I got an idea and solved my problem. Do you think there can be a better way to solve this problem. Thanks in advance.

[catweasel]

Yes, these values are coming from Database,

Actually there are three columns StartDate (DateTime), EndDate (DateTime) and Length (Time), and I have to calculate the difference in time between two dates. That was my actual problem. I was having difficulties to calculate the time difference as I was unable to find any built in function that would do the job for me so searching on net I found this page http://www.gidnetwork.com/b-16.html. So I got an idea and solved my problem. Do you think there can be a better way to solve this problem. Thanks in advance.

I assume this is a MySQL database.

MySQL has already anticipated this kind of problem and supplied you with the appropriate functions.. DATEDIFF and TIMEDIFF. You don't need (and shouldn't) try to solve this in PHP.. do it in the query.

MySQL Date and Time Functions

[xiaawan]

I assume this is a MySQL database.

You are right, it's MySQL Database. I am not sure why I did not use MySQL command to solve my problem although you also mentioned this in your earlier post in this thread. So now I am using the following code.

SELECT TIMEDIFF('2007-10-05 12:10:18','2007-10-05 16:14:59') AS length;

And it gives me the right output that I needed. Thanks for your help, in this scenario I see this one the best solution. Again thank you very much and the link you provided is also very helpful. See you next, have a nice time

[volomike]

xiawann:

If I have two DateTime() objects, and I want to see which one is older, I just do:

$sDate1 = the date in 'Y-m-d H:i:s' format for the proper timezone
$sDate2 = ditto, but a separate date you want to compare

$d1 = new DateTime($sDate1);
$d2 = new DateTime($sDate2);

if ($d1 > $d2) {
echo 'd1 is older';
} else {
echo 'd2 is older';
} 

Also, you can set the timezone and do +/- by given factors. So, if I have a subscription timestamp for a website feature, and I store this in my database as '2007-12-07 01:13:55' (Y-m-d H:i:s format), and I only deal with GMT values, and I want to see if the 12 month subscription has run out, I could do:

date_default_timezone_set('Europe/London');
$dDate = new DateTime($sDateTimeFromDatabaseStoredInGMTFormat);
$dDate->modify('+12 months');
// note below I don't have to pass gmdate('Y-m-d H:i:s') because 
// this is done for us when empty
$dNow = new DateTime(); 

if ($dDate <= $dNow) {
echo 'user needs to renew';
} else {
echo 'user does not need to renew';
}