I have a PostgreSQL database with events. Each event has a datetime or an interval. Common data are stored in the events
table and dates are stored in either events_dates
(datetime
field) or events_intervals
(starts_date
, ends_date
both are date
fields).
Sample datetime events
- I was born on 1930-06-09
- I got my driver's license on 1950-07-12
- Christmas is on 1900-12-24 (1900 is reserved for yearly reoccuring events)
Sample interval events
- I'll be on vacation from 2011-06-09 till 2011-07-23
Now I have a user that will want to look up these events. They will be able to fill out a form with from
and to
fields and in those fields they can enter full date, day, month, year, day and month, day and year, month and year in one or both fields.
Sample queries
- From May 3 to 2012 December 21 will look for events between May 3 and December 21 whose max year is 2012
- From day 3 to day 15 will look for events between the 3rd and 15th day of every month and year
- From day 3 will look for events on the 3rd day of every month and year (same if
from
is empty andto
is not) - From May 3 to June will look for events between May 3 and last day of June of every year
- etc.
Any tips on how to write a maintanable query (it doesn't necessarily have to be fast)?
Some things that we thought of
- write all possible
from
,to
and day/month/year combinations - not maintable - compare dates as strings e.g. input:
____-06-__
where_
is a wildcard - I wouldn't have to generate all possible combinations but this doesn't work for intervals