如何查找日期是否适合PHP或MySQL间隔？ [英] How to find if a date fits an interval in either PHP or MySQL?

问题描述

假设我有一个日期时间，2011年6月16日7点。我想能够在2011年8月5日的7点检查，并且能够说明它是从第一个日期起的1天的倍数，而7:01不会计数，因为它不是精确多个。另外一个测试集：假设我们在2011年6月16日有7点钟，我想检查一下特定的分钟是否在那以后的时间内。 。所以9点，11点，13点等等，不过9点半到10:00不行。这可能会持续数天和数月 - 9月1日7:00仍然会在每2小时内计算。 （不，现在我不知道我会怎么处理DST：D）

我想了一会儿，不能想任何已经存在于PHP或MySQL中的任何东西都可以轻而易举地完成，但是可以这样做，所以我想在开始重新创建轮子之前先抛出来。

在PHP 5.1上，可悲的是，

解决方案

  select * 
 from test 
其中datetimefield> '2011-06-16 07:00:00'
和
 mod（timestampdiff（second，'2011-06-16 07：00：00'，datetimefield），7200）= 0 
  

此示例将为您提供大于2011-06-16 07:00:00的所有记录，字段正好是2小时的倍数。

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.

这篇关于如何查找日期是否适合PHP或MySQL间隔？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！