7

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.

Zando
  • 5,473
  • 8
  • 30
  • 37
  • Is it a requirement to have all events stored into one table? – Rogach Jan 14 '12 at 18:00
  • Not necessarily. I was thinking now that it makes more sense to keep routine things, like regular operating hours, in a separate table, with separate fields. I was holding out hope that there was a bitwise solution to all of this – Zando Jan 14 '12 at 20:02
  • @Zando: bitwise solution is possible e.g., [`cron.c`](http://opensource.apple.com/source/cron/cron-2/cron/cron.c) but you shouldn't need it: range queries can be efficient (O(log(N) for indexed boundaries or R*Tree index). – jfs Jan 15 '12 at 01:56
  • OpenTSDB? Or are you looking more for something like Calendar Queue used by things like Cron? – CMCDragonkai Jun 04 '15 at 08:19

2 Answers2

3

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.

Savino Sguera
  • 3,522
  • 21
  • 20
  • Kudos to you for giving me some ideas on how to just model the "1st/2nd/3rd/4th [of the month]" problem. I'm going to think over how this could be applied to a query in which there is a given day, and seeing which events include that day in their timeframes. For example, Jan. 12 should include all second Thurs. events, as well as weekly Thurs. events, and any events that only happen on Jan. 12 – Zando Jan 14 '12 at 22:54
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).

Community
  • 1
  • 1
jfs
  • 399,953
  • 195
  • 994
  • 1,670