I want to store partial information related to a date. I might know the year and month but not the day. I might know the day and month but not the year. I might know the date lies in an open or closed interval. What are my options for modeling this type of data?
I've done this for attorneys in the past. I used an ISO-style date format (yyyy-mm-dd) stored as char(10). Any missing part used question marks.
2002-01-?? -- some day in January 2002 199?-??-?? -- some day in the 1990s ????-01-08 -- January 8th in an unknown year
Values like these are intuitive to people doing data entry, they sort fairly sensibly, and the format can be controlled with CHECK constraints. You lose date and time arithmetic, but when you have unknown dates that usually doesn't matter too much.
I might know the date lies in an open or closed interval.
That's actually a different kind of information than, say, knowing that something happened on April 1, but not knowing which year. Off the top of my head, you could store four columns.
- starting date
- ending date
- whether the starting date is or is not in the interval
- whether the ending date is or is not in the interval
I'd expect to have to store that kind of information separately from the kind of data I mentioned first. "January 8th in an unknown year" would be particularly troublesome to represent in either an open or closed interval.
The latest release of PostgreSQL (9.2) includes support for range types.