How to find if a date fits an interval in either PHP or MySQL?
Let's say I have a datetime, June 16 2011 at 7:00. I want to be able to check at, say, August 5 2011 at 7:00 and be able to tell that it is exactly a multiple of 1 day since the first date, whereas 7:01 would not count, since it is not an exact multiple.
Another test set: Let's say we have June 16 2011 at 7:00, and I want to check if a particular minute is within an interval of exactly 2 hours since then. So 9:00, 11:00, 13:00, etc. would count, but 9:30 and 10:00 would not. And this could continue for days and months - September 1 at 7:00 would still count as within every 2 hours. (And no, at the moment I don't know how I'm going to handle DST :D)
I thought about it for a moment and couldn't think of anything already existing in PHP or MySQL to do this easily but hell, it could, so I wanted to throw this up and ask before I start reinventing the wheel.
This is on PHP 5.1, sadly.
select * from test where datetimefield > '2011-06-16 07:00:00' and mod(timestampdiff(second,'2011-06-16 07:00:00',datetimefield),7200) = 0
This example will give you all the records greater than '2011-06-16 07:00:00' where the field is exactly a multiple of 2 hours.
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]