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
"2011-03-10" and my time column was
"14:30:00". So I had to combine them before making the comparison with
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
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';
MySQL – Combine date and time fields – FusionCoder
Image credit: Flickr
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.