getButterfly Logo getButterfly code wrangling since 2005

PHP time and date notes

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

Image credit: Flickr

Subscribe to getButterfly Blog

Once a week or so we send an email with our best content. We never bug you, we just send you our latest piece of content.

If you found any value in this post, agree, disagree, or have anything to add - please do. I use comments as my #1 signal for what to write about. Read our comment policy before commenting! Comments such as "Thank you!", "Awesome!", "You're the man!" are either marked as spam or stripped from URL.

Leave a reply