In a PostgreSQL table, I'd like to store events which have a range telling when the event is happening. However, some events span whole days (i.e., the user does not care about time), while others must contain time as well. The question is how to store such data.

A typical query would be to list all events in a given date range, or date/time range, while the whole-day events shall be contained for any time of the given date. E.g., if listing events in range (2015-02-04 14:00:00, 2015-02-06 08:00:00), the result shall contain an event happening in range (2015-02-05 16:00:00, 2015-02-09 13:00:00) as well as an event happening in range (2015-02-03, 2015-02-04).

Three possible solutions occur to me:

  1. Store date and time parts of event ranges separately, time being optional (i.e., for whole-day events, NULL would be stored in the time part, and treated separately when querying).
  2. Store the range in a TSRANGE and have a @wholeday@ flag telling whether the time parts shall in fact be ignored (and treated separately when querying).
  3. Store the range in a TSRANGE, while for whole-day events, ensure the time is always 00:00:00 and 24:00:00 for the range start and end, respectively.

I tend to use the third option, but would like to ask for experience with the given problem, as well as other possible solutions to it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.