Tweet

I’ve always sucked at date/time calculations using PHP or MySQL. This changed a while ago when I broke the ice with a Facebook-style function for calculating time diferrence in a friendly way.

Today, I had to calculate past dates using 2 separate date and time columns. MySQL uses NOW() to output a date format such as "2011-03-10 14:30:00". My date column was DATE() – "2011-03-10" and my time column was TIME() – "14:30:00". So I had to combine them before making the comparison with NOW().

The MySQL function for this comparison is:

SELECT * FROM mytable WHERE DATE_ADD(mydate, INTERVAL mytime HOUR_SECOND) <= NOW()

This function will combine both columns into a TIMESTAMP value, formatted by DATE_ADD, ready to compare with NOW() format.

My documentation included more examples such as: SELECT TO_SECONDS('2011-03-10 14:30:00'); and SELECT TO_SECONDS(NOW());. Calculating the time diferrence in seconds is easy using these two MySQL functions, though time values such as 00:00:00 will fail.

This is how you format dates any way you want:

date('d/m/Y', strtotime($mydaterow))
date('H:i', strtotime($mytimerow))

This line will display the diferrence in minutes between two dates:

$toTime=strtotime('2010-10-10 13:48:00');
$fromTime=strtotime('2010-10-10 13:22:00');
echo round(abs($to_time - $from_time) / 60,2)." minute";

Imagine you can replace the date strings with your own date/time column(s).

And another MySQL line that will select rows within a range of time:

SELECT * FROM table WHERE DATE_ADD(date, INTERVAL time HOUR_SECOND) BETWEEN '2010-01-01 16:30:00' AND '2010-01-02 17:00:00';

Useful links:

MySQL – Combine date and time fields – FusionCoder