How to find if a date fits an interval in either PHP or MySQL?


Problem :

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.



Solution :

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.


Mysql Tutorials

Mysql Howto..

How to find all records which are NOT in this array? (MySql)

How to dynamically get the value of a select tag from MySQL table

How to create product table in which each product is under multiple categories in MySQL?

How to make execution update reverse query mysql by id?

How to implement mysql compress() function in php

how to merge tables using mysql database?

How to troubleshooting a MySQL fatal error? C# front end

mysql select distinct - show value and totals

How can I update MySQL table value on Future Date?

How do I implement a trigger in MYSQL to prevent insertion when row has duplicate data?

SQL query- How does the following work?

How to get Count in Hibernate using JPA?

how to insert xml to mysql with php [duplicate]

How to execute different query based on the result got from executing query in mysql?

How to delete sleep process in Mysql

Mysql UPDATE syntax, how to use as array

How do I diagnose steps in a trigger on MySQL

How do I combine a MySQL query and subquery when there's multiple rows?

How to structure MySQL database to show link records in PHP under a Parent directory type structure?

How to get the max value from a column with alphanumeric strings?

How to count similar interests in MySQL

How to join two mysql querys from two tables

How can I query for all records within latitude / longitude area?

MySQL - How to use subquery into IN statement by value

How to take column backup for a table in mysql [closed]

I would really like to use PHP and MySQL persistent connections, but how?

How to optimize this MySQL query? (moving window)

MySql self join predicate not understanding how it works

how to select two columns from two tables in mysql

Appfog with node.js how to access with javascript to mysql databases?