Geek asks and answers

How to determine if a date matches an interval in PHP or MySQL?

Let's say I have a date, June 16, 2011 at 7:00. I want to be able to check, say, on August 5, 2011 at 7:00, and be able to say that it is exactly a multiple of 1 day from the first date, while 7:01 will not be considered, since this is not an exact few.

Another test suite: let's say we have June 16, 2011 at 7:00, and I want to check whether a particular minute with an interval of exactly 2 hours since then. Thus, 9:00, 11:00, 13:00, etc. They will be considered, but 9:30 and 10:00 will not. And this can continue for several days and months - on September 1 at 7:00 it will still be considered every 2 hours. (And no, at the moment I don’t know how I will handle DST: D)

I thought about it for a moment and couldn’t think about what already exists in PHP or MySQL to make it easy, but hell it could, so I wanted to give it up and ask before I started reinventing the wheel.

This is on PHP 5.1, unfortunately.

+3
source share
5 answers
select *
from test
where datetimefield > '2011-06-16 07:00:00'
and
mod(timestampdiff(second,'2011-06-16 07:00:00',datetimefield),7200) = 0

In this example, you will get all records that exceed "2011-06-16 07:00:00", where the field is exactly a multiple of 2 hours.

+1
source

The easiest way would be to convert the date / time values ​​to a unix timestamp, and then just do some subtraction / division:

2011-06-16 07:00:00 -> 1308229200
2011-08-05 07:00:00 -> 1312549200
2011-08-05 07:00:01 -> 1312549201

1312549200 - 1308229200 = 4320000 / 86400 = 50 (days)
1312549201 - 1308229200 = 4320001 / 86400 = 50.0000115...

So in other words:

if (($end_timestamp - $start_timestamp) % 864000)) == 0) {
  ... even multiple ...
}

The same could be done for day / week comparison. For several months it will be in the window, because the months are not very pleasant even to deal with them.

+1

MySQL Date:   http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

  • TIME(), . , .

  • - / , , , , - . (, 5) "" , , int.

0

PHP, : PHP

, , .

0

DateTime diff(). DateInterval - // .

, .

0

More articles:


All Articles