query to find recurring events

[sneakyimp]

i have a db table:

events
id
title
start_stamp (unix timestamp)
end_stamp (unit timestamp)
start_date (mm/dd/yyyy)
start_m (mm)
start_d (dd)
start_y (yyyy)
end_date (mm/dd/yyyy)
recurring (0 or 1)
recur_freq (weekly, biweekly, or monthly)
recur_times (int between 1 and 12)

Given a search window, i want to detect any events that occur within my search window.

So far, I have something like this:

define('EVENT_DURATION_THRESHOLD', 60*60*2); // two hours
$search_start = mktime(0,0,0,2,1,2006);
$search_end = mktime(0,0,0,2,5,2006);
$sql = "SELECT * FROM events WHERE
   (start_stamp >= " . $search_start . " AND start_stamp <= " . $search_end . ") OR ((e.start_stamp < " . $search_start . ") AND (e.end_stamp IS NOT NULL) AND (e.end_stamp > " . ($search_start + EVENT_DURATION_THRESHOLD) . " ))";

But what is KILLING ME is the recurring events. some events which have recurring = 1 and recur_freq specified as weekly/biweekly/monthly. how can i determine if a recurrence of one of these events will over lap with my search window?

Complicating matters is DAYLIGHT SAVINGS TIME. Whose idea was that anyway? Ben Franklin? Asshole. Anyways, check this out:

<?
$event_start = mktime(8,0,0,2,15,2006);
$recur_period = 60*60*24*7; // 7 days
for($i=0; $i<10; $i++) {
  $tmp_time = $event_start + ($i*$recur_period);
  $tmp_end = $tmp_time + $d;
  echo '&nbsp;' . ($i+1) . ' - ' . date('M d, Y \a\t G:i:s', $tmp_time)  . '<br>';
}

?>

Here is what comes out...time shifts from 8pm to 10 pm when DST kicks in:

1 - Feb 15, 2006 at 8:00:00 to 8:00:00
2 - Feb 22, 2006 at 8:00:00 to 8:00:00
3 - Mar 01, 2006 at 8:00:00 to 8:00:00
4 - Mar 08, 2006 at 8:00:00 to 8:00:00
5 - Mar 15, 2006 at 8:00:00 to 8:00:00
6 - Mar 22, 2006 at 8:00:00 to 8:00:00
7 - Mar 29, 2006 at 8:00:00 to 8:00:00
8 - Apr 05, 2006 at 9:00:00 to 9:00:00
9 - Apr 12, 2006 at 9:00:00 to 9:00:00
10 - Apr 19, 2006 at 9:00:00 to 9:00:00

make this change to straighten out the times.

//  $tmp_time = $event_start + ($i*$recur_period);
  $tmp_time = strtotime('+' . ($i*$recur_period_days) . ' days', $event_start);

I am so confused about this. As far as i know the website will never need to display those dates...i just noticed it while working on this. On the OTHER hand, if i come up with some kind of query i'm wondering if it could possibly work accurately.

[Roger_Ramjet]

OK, the solution to all this is to break your db 'normalisation'. Essentially, data is entered once and read many. So what you would end up with is performing the same complex date calculation to find actual event recurances every time you query the database. Common sense dictates that you just run those calculations ONCE and store the results for later referance. You have in any case done something like this with your multiple date and time columns instead of just storing them once and using date_format in your queries to calculate dd, mm, yyyy from the start date columns.

So, you need to make a sub-table for events that stores the schedule only while the main table stores the other details. You would then have multiple entries for events that recur. Querying the calendar will be much easier and faster that way.

[sneakyimp]

Thanks Roger. I think you have hit the nail on the head...in particular thanks for your recommendation about storing the event info in one table and simply using an assoc table for events to dates. i was thinking before about just posting duplicate events.

I'm now wondering about multi-day events and whether those would need multiple association records or whether I can get away with storing start/end/duration data with the event itself. I'm also wondering whether to have those association records store exploded versions of the date (one field each for m/d/y) or whether i should use a unix timestamp or a datetime field. Embarassingly, I pulled an all-nighter this week figuring out a query for finding recurring weekly and biweekly events (i'm math-slow sometimes) and only after realized that a) I have this annoying DST problem mentioned above and b) that monthly recurrence is a whole different kettle of fish.

I'm also wondering how you would make sure that we continue to create association records in the future so that monthly recurrences don't 'run out' at some point in the future.

[sneakyimp]

by the way, after a long nite of banging my head against the weekly recurring event concept, i did manage to find a fairly tight SQL clause which could detect a recurring event. I have NO IDEA what the performance might be like.

NOTE: It DOES NOT handle DST changes because the intervals don't apply across a DST time change--the number of seconds between 2pm wednesday and the following wednesday 2pm is either more or less than 606024*7 by one hour.

// weekly
  $recur_period = 60*60*24*7;
	$and_sql .= "AND ((e.recur_freq='weekly') AND (((" . ($search_start + EVENT_DURATION_THRESHOLD) . ") <= (e.start_stamp + ((e.recur_times-1)*" . $recur_period . ") + IF(e.end_stamp IS NULL, 0, e.end_stamp-e.start_stamp))) &&
    (e.start_stamp <= " . $search_end . ") && 
    (((" . $recur_period . " - ((" . $search_start . " - e.start_stamp) % " . $recur_period . ")) <= " . ($search_end - $search_start) . ") || ((IF(e.end_stamp IS NULL, 0, e.end_stamp-e.start_stamp) - ((" . $search_start . "-e.start_stamp)%" . $recur_period . ")) >= " . EVENT_DURATION_THRESHOLD . "))))";

[Roger_Ramjet]

Got no use for unix timestamps myself. I may have used them 20 years ago, but if so I would have found them a pain. As it is they are not neccessary now as all the languages, databases and operating systems that I use have fully functional date data types and functions that cater for time-zones and dst as well.

Up to you how you store your data - there is no 'right' way, only what works for your application - though there are always wrong ways. The trade-offs are the disk space taken up by extra fields against the saving in processing time when using the data, and the extra overhead when inputting or updating records which can be quite a lot if you have indexes on the extra columns.

If you don't want to have too many advance entries for recurring events then you will have to run a job to generate them at regular intervals.

[sneakyimp]

Roger Ramjet wrote:

Got no use for unix timestamps myself. I may have used them 20 years ago, but if so I would have found them a pain. As it is they are not neccessary now as all the languages, databases and operating systems that I use have fully functional date data types and functions that cater for time-zones and dst as well.

I'm storing both for now until i find my way through this. I know that the unix timestamps apparently suffer from the Y2.037K problem (timestamps no workie past 2037). My db is now happily denormalized and i can see that the queries are certainly simpler and will likely be faster because most of the query now is simply inequality comparisons.

It did require a change to the input form. Events are now entered one day at a time and each event gets its own date, start time, and stop time. Recurring events result in a number of event-time association records as do multi-day non-recurring events.

I'm still having a little trouble with the query, though, because the search i'm working on is supposed to find events between some start_date and end_date that happen between some start_hour and some end_hour:

events
id
title

event_time_assoc
id
event_id
start_timestamp
start_datetime
end_timestamp
end_datetime

getting an event that starts within the date range is pretty easy:

$search_timestamp = mktime(0,0,0, 10, 5, 2006); // october 5
$end_timestamp = mktime(0,0,0, 11, 5, 2006); // november 5

$sql = "SELECT e.id, e.title, esa.start_timestamp, esa.end_timestamp
        FROM events e, event_time_assoc esa
        WHERE esa.event_id=e.id
        AND esa.start_timestamp >= " . $search_timestamp . "
        AND esa.start_timestamp <= " . $end_timestamp

What I'm wondering is how to make sure the event date is not only within the search range but the event time of day is also in an hour range on any given day within the search date range.

Roger Ramjet wrote:

If you don't want to have too many advance entries for recurring events then you will have to run a job to generate them at regular intervals.

I'm guessing the only reliable way to do this is with a CRON job.