How to represent dates with uncertainty in PostgreSQL
There are several different ways to approach fuzzy dates. In PostgreSQL, you can use
- a pair of date columns (earliest_possible_date, latest_possible_date),
- a date column and a precision column ('2012-01-01', 'year'), or
- a range data type (daterange), or
- a varchar ('2013-01-2?', '2013-??-05'), or
- another table or tables with any of those data types.
The range data type is peculiar to recent versions of PostgreSQL. You can use the others in any SQL dbms.
The kind of fuzziness you need is application-dependent. How you query fuzzy dates depends on which data type or structure you pick. You need a firm grasp on what kinds of fuzziness you need to store, and on the kind of questions your users need answered. And you need to test to make sure your database can answer their questions.
For example, in legal systems dates might be remembered poorly or defaced. Someone might say "It was some Thursday in January 2014. I know it was a Thursday, because it was trash pick-up day", or "It was the first week in either June or July last year". To record that kind of fuzziness, you need another table.
Or a postmark might be marred so that you can read only "14, 2014". You know it was postmarked on the 14th, but you don't know which month. Again, you need another table.
Some (all?) of these won't give you three-valued logic unless you jump through some hoops. ("Possible" isn't a valid Boolean value.)
To add to what Mike posted I would use date comments such as:
date Comment-------------------------------------------------------------------1/1/2010 Sometime in 20107/8/2014 Customer says they will pay the second week in July1/1/2015 Package will arrive sometime next year in JanuaryAlso, you can use date parts. Create a separate column for the Year, Month, and Day. What ever in unknown leave it blank.