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.

See the better solution after the jump »