3

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.

Andrew
  • 5,095
  • 6
  • 42
  • 48

4 Answers4

1

Easiest would be to convert the date/time values into a unix timestamp and then simply 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 ...
}

Same would hold for the day/week comparisons. For months, this'll be out the window, since months aren't nice even figures to deal with.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Was hoping to be able to do months in this... hm. However, that's actually pretty easy: Just check the day of the month. If it's equal, run it. And don't let them schedule things for the 29th, 30th, or 31st, without an additional option for simply "last day of month" :) – Andrew Jun 09 '11 at 21:05
  • If this is for a booking system, and you want to restrict to these types of intervals, then don't allow arbitrary date inputs. Have the script spit out dropdowns that list only the allowable intervals. – Marc B Jun 09 '11 at 21:09
1
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.

Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
0

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

  1. You can use TIME() to get just the time part of a date. If the time parts are the same it is an exact multiple.

  2. For the two hour thing, one way to do it would be to get the minute/seconds part of the date, make sure those are equal, then make sure that the hour parts of the dates are both even or both odd. For more complicated integer (e.g. 5) hour multiples, you can "fake" doing a mod by dividing the hour parts and checking if the result is an int.

Phil
  • 179
  • 3
  • Oho, good point. 7:00 will always be 7:00, etc. But, what if I said, June 15, 7am, and I wanted something to tick every *5* hours. Odd number, so June 16 at 7am would not qualify. :) – Andrew Jun 09 '11 at 21:04
0

You can compare two DateTime objects via diff() method. Result is a DateInterval object - you can check the exact number of days/hours/minutes between two dates.

It's useless to write your own algorithms if you can use built-in functionality.

Ondřej Mirtes
  • 5,054
  • 25
  • 36
  • Unfortunately, and I apologize for not including this in my post, but I am currently stuck in PHP 5.1. – Andrew Jun 09 '11 at 21:22
  • You should ask for PHP 5.3. Bothering with 5.1 in 2011 is just a waste of time. – Ondřej Mirtes Jun 09 '11 at 22:50
  • Oh, I have. But we're locked in to RHEL 5.whatever that only officially supports PHP 5.1. (it has 5.3 but not with the addons we want) – Andrew Jun 10 '11 at 13:26