How to compare elapsed time between datetime fields?

Question

Given 2 fields of type datetime in mySql which look like: 2009-07-26 18:42:21. After retrieving these values in PHP, how can I compare the 2 time stamps to figure out how many seconds have elapsed between them? I tried simply subtracting them but that didn't to work.

Answer 1

$ts1 = strtotime('2009-07-26 18:42:21');
$ts2 = strtotime('2009-07-26 18:42:20');

$elapsedSeconds = $ts1 - $ts2; // = 1

Answer 2

select them as unix timestamps and just subtract one from the other

SELECT UNIX_TIMESTAMP(field1) AS time1, UNIX_TIMESTAMP(field2) AS time2

Answer 3

Try this PHP code to compare the dates;

<?php
$date_difference_in_seconds = abs(strtotime($date_1) - strtotime($date_2));
?>

The dates that MySQL stores are in a different format to that PHP uses. PHP stores dates in seconds elapsed since the Unix Epoch, which you can read more about at http://en.wikipedia.org/wiki/Unix_time. This one line of code simply converts the two dates to PHP format, and subtracts the two dates from each other before absoluting the value so as to avoid negative results.

If you don't want to use the date in the format that MySQL returns at all, but are just simply after subtracting them, use a straight out SQL query instead to conserve memory:

SELECT TIME_TO_SEC(TIME_DIFF(date_1, date_2)) AS date_difference_in_seconds FROM table;