Random PHP/MySQL discovery: time differences

I had been plagued by a nagging question while developing a PHP application: how do I calculate the difference between two timestamps, to check whether the timestamps are within x minutes of each other?

My initial solution wasn’t at all perfect, although it was still better than developing an algorithm from scratch to decipher timestamps into hour/minute/second objects and coding math.

Solution 1: MySQL’s TIMESTAMPDIFF()

My first solution was to use a function native to MySQL, TIMESTAMPDIFF(). This function takes in three parameters: the unit of time in which the return value will be, and two datetime expressions.

To query whether a given timestamp was within 15 minutes (either +/-) of the current UTC timestamp, I used this statement:

SELECT ABS(TIMESTAMPDIFF(MINUTE, *********, UTC_TIMESTAMP())) < 15

It worked, but I wasn’t satisfied with having an extra query just to verify a timestamp. Besides, I was concerned about speed; that one query takes about 0.004 seconds to execute, which was too much for me.

Then I discovered the native Date/Time extension, built-in on PHP 5.2 and above.

Solution 2: PHP’s Date/Time classes

Note: although Date/Time has been in PHP since 5.2, I use a few methods added only in 5.3.0.

I found out that there are native PHP classes to represent dates and times, and intervals of time. This solution to checking differences is intuitive and object-oriented (although there are aliases of the OOP methods to do so procedurally). I’ll talk about performance after the code.

First, I need to assert that both are in the same timezone. Since I’m using the current UTC timestamp as a reference (and have no idea whether the PHP application will run on a server where the default timezone is UTC), I need to set the timezone explicitly.

$utc = new DateTimeZone('UTC');

This object will be provided in the construction of the DateTime objects:

$now = new DateTime(null, $utc);
$given = new DateTime($yourTimestampHere, $utc);

If your timestamp uses a format that is non-standard, you may want to use DateTime::createFromFormat() instead.

Once we have two DateTime objects, the diff() method added in PHP 5.3.0 allows us to compare them:

$interval = $now->diff($given, true); // the second param means absolute difference

The return value of diff() is a DateInterval object and I use its properties to check that the difference is within 15 minutes: (this is the return statement of my difference-checking function)

// interval is 0 days, 0 hours and up to 15 minutes
return ($interval->days == 0 && $interval->h == 0 && $interval->i < 15);

Comparing Performance

I’ve found that the PHP code, despite its heavy use of objects, is about 4x as fast as the MySQL statement when called once during the execution of a script. When benchmarked, PHP is up to 9x faster than the MySQL query over 10000 iterations.

You can try the benchmark for yourself by loading the script at http://gist.github.com/556777 onto a PHP 5.3 server (note: you must also have a valid MySQL server configured in the script). Alternatively, the same code should run from the command line with the PHP interpreter.

Actual Deployment

Since adoption of PHP 5.3 has been slow, the Date/Time features used may not be available on the majority of production environments. The best way to deal with this is to check the PHP version at runtime and fall back to using MySQL when the version is older than 5.3.0.

if (version_compare(PHP_VERSION, '5.3.0') >= 0) {
    // PHP 5.3 code here
} else {
    // MySQL query here
    // The additional milliseconds of using the MySQL query is
    // an incentive to upgrade to PHP 5.3, especially in production!
}

(Since PHP is an interpreted language, older versions of PHP will not throw errors upon encountering unknown classes or methods inside the if block because it is never executed. To my knowledge, this behaviour is similar to using something like #if defined COMPILER_GCC in C—that block won’t be executed on other compilers.)

Was this useful?

If you have other solutions, please comment and let me know! Feel free to comment if this helped you. :)

If you found this useful and want to receive blog posts via e-mail, subscribe! We promise not to send any unsolicited junk.