proper way to handle inequality comparisons on date fields?

sneakyimp

I'm considering using a mysql DATE field in a table and am wondering how i might retrieve records with start and end DATE fields in a certain range.

my search dates will be entered american style (mm/dd/yyyy). I would like to put them into vars called $search_start and $search_end. I can reformat them however necessary to get my query working...

which brings me to my query. supposing i have a bunch of records in a table called 'table' which has fields start_date and end_date, how do i retrieve records that overlap my search range? Would the following query retrieve records that overlap my search range (meaning the start date or end date of any given record occurs between $search_start and $search_end)?

// explode the start/end dates because mysql requires YYYY/MM/DD
// rather than mm/dd/yyyy
$search_start = '9/30/2006';
$sp = explode('/', $search_start);
$start = $sp[2] . '-' . $sp[0] . '-' . $sp[1];
$search_end = '10/15/2006';
$ep = explode('/', $search_end);
$end = $ep[2] . '-' . $ep[0] . '-' . $ep[1];

// select records whose time span overlaps my start and end range
$sql = "SELECT * FROM table WHERE
        (start_date >= '" . $start . "' AND start_date <= '" . $end . "')
        OR
        (end_date >= '" . $start . "' AND end_date <= '" . $end . "')";

Sorry if this seems like an obvious or trivial question. I was reading on the mysql docs site about DATE and DATETIME data types and all the related functions but there seemed to be nothing about simple inequality comparisons. It's not clear to me if I should first convert them with UNIX_TIMESTAMP or not and also if doing so would result in any Daylight Savings Time weirdness.

snoopykiss

In all of my applications, I use UNIX timestamps instead of MySQL dates. I find that it makes these types of comparisons much easier, because then you're comparing integers.

I will admit that it makes debugging more complicated because you can't just look at the field in the DB and know what it is and if it matches, but it does push back on you to make your UI better so that you're using it to admin your application, rather than looking into the DB.

I don't believe that you're going to resolve any of the DST weirdness through this route either.

Probably not the advice you're looking for, but that's from my experience.

sneakyimp

Sadly, it was using timestamps that led me to this question. I was storing some records that described recurring events using a timestamp and realized that it was going to be impossible to do a search on those events for recurring monthly events because you never know whether a month is going to have 28 days, 29 days, 30 days, or 31 days.

I'm still trying to wrap my head around the DST weirdness and as far as I can tell, it only seems to rear its head when we're either talking about a span of time that straddles a change in DST or when we compare time stamps generated by PHP versus timestamps generated by MySQL. There was a post in the MySQL docs which insisted that MySQL timestamp functions were not hip to DST changes where as PHP ones were.

That said, you still might be right. I might be able to use timestamps. I guess I was worried about the Y2.037K problem (timestamps after the year 2037 are undefined because the second count exceeds MAXINT). The neato mysql DATE format supports years up to December 31st 9999 😉

If MySQL can easily compare dates in a field of type DATE I was hoping to use that. On the other hand, different versions of MySQL might behave differently. There are so many considerations!

So if I'm forced to use unix timestamps, I think I can avoid DST by doing all the date<-->timestamp conversions in PHP and just limit all my queries to checking timestamps.

snoopykiss

Actually, using a timestamp, you CAN see how many days are in a month.

http://us2.php.net/date (date 't', $timestamp)

Does that solve your problem?

sneakyimp

which problem?

If you're talking about coming up with a query to determine whether recurring events overlap with a particular timeframe, NO. The reason has to do with the fact that the start time, end time, recurrence properties, etc. are in the database and the calculation therefore lies in the mysql domain rather than the php domain.

furthermore, while it is possible to construct a query to determine whether a single recurring event intersects a particular time frame (a pretty nasty query involving an and/or clause for each occurrence of the event) it is not possible (as far as i know) to construct a single query to determine if ALL events stored in a database (recurring or not) intersect with a particular time frame.

go ahead and try it if you want. i dare you! use this event table structure...imagine it contains a million records in it:

EVENT
id
start_date <--any format you like
end_date <-- NULL or any format you like
start_hour <--any format you like, i chose 0-23
end_hour <-- NULL or any format you like, i chose 0-23
recurs <-- 1 if this event recurs, 0 of not
recur_freq <-- weekly, biweekly, or monthly
times <-- how many times the event happens in total

There's more on the particulars of locating a recurring event here:
http://phpbuilder.com/board/showthread.php?t=10330062