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.