In a database, how to store event occurrence dates and timeframes for fast/elegant querying?

Question

Let's say I'm maintaining a database of events, which could be anything from the weekly schedule of a business's operating hours (Monday-Friday: 10AM-7PM, Sat: 12PM-6PM, Sun: Closed) to monthly events (Art fair, every first Saturday, from 10AM - 5PM) to an annual event (Christmas Eve charity dinner, 7PM-9PM)

In an ideal situation, I would like to query the database with any given day, say: Thursday, Jan. 12, 2012, 12PM

...and find all events that are occurring on:

• a regular Thursday noon (such as a business being open)
• every second Thursday (an art fair)
• Jan. 12, specifically (er...Turkmenistan Memorial Day)

I suppose there's no point in talking about the query construction without first thinking how such dates/times would be stored in a database.

I can't think of how the concept of regular weekly operating hours (even ignoring edge cases) could be modeled in a one-record, single field that would also model a once-every-year event. At the very least, it seems like I would need at least five fields:

• Start-time
• Duration
• Day of the week (e.g. Mon. Tues., etc)
• optional absolute annual date (e.g. May 8)
• optional monthly occurrence (fourth Wednesdays)

And I'm guessing there's no way to encapsulate this in a single row, right? For example, a business open every weekday would have five records.

And the end goal is to be able to do a relatively elegant query that could find all event-records which contain a given moment in their timeframe.

Answer 1

Not sure if you are asking specifically for a dbms-agnostic solution, anyway in postgresql (and I think it's possible in most RDBMS) you can store Timestamps and get a lot of info out of them: http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html.

Postgresql-specific answer

9.9.1. EXTRACT, date_part

EXTRACT(field FROM source) The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. (Expressions of type date are cast to timestamp and can therefore be used as well.) field is an identifier or string that selects what field to extract from the source value. The extract function returns values of type double precision. The following are valid field names: [...] day, DOW (day of week), DOY (day of year), [...]

So for instance, to select events occurring each 2nd Thursday you could write:

SELECT * FROM events  #your table
    WHERE EXTRACT(DOW FROM date_column)=4 # DOW  goes from sunday (0) to saturday (6)
    AND EXTRACT(DAY FROM date_column)>7 # must occurr after 7th day of the month (ie. if the 1st is a Thursday
    AND EXTRACT(DAY FROM date_column)<15 # and before the 15th (ie. if the 1st is a Friday, so the first Thursday is on the 7th, and the second is on the 14th)

To model duration you may simply have 2 timestamp fields, for the start/end of the event.

Also note that you can add or subtract timestamps, as well as know if intervals overlap.

In general, I would first try to use datatypes and functions provided by your DBMS, and only if you cannot really find a solution try to model it yourself.

Answer 2

if you maintain the next start/end time for each event then the query is simple: select all events where start time <= timestamp < end time.

How to maintain such table efficiently depends on your application. See how existing calendar/todo/cron-like applications are implemented. For example, you could update all events that have their end time in the past periodically or before each query.

The advantage of this approach is that you could use an arbitrary complex logic for calculating the next start/end time.

And I'm guessing there's no way to encapsulate this in a single row, right?

You could store a single field: "Rule" that describe in a crontab-like language when the event occurs (example: parse crontab entry, calculate next start time).