PHP/MySQL Date and Time Notes

submit to reddit

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:


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 fieldsFusionCoder

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>