How to represent dates with uncertainty in PostgreSQL
database, date, postgresql, sql
Solution
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.)
Problem
PostgreSQL provides the `date` format datatype to store dates. The problem with these dates is however they can't - as far as I know - reason about uncertainty. Sometimes one does not know the full date of something, but knows it happened in January 1995 or in "1999 or 2000" (`date2`). There can be several reasons for that: - People don't remember the exact date; - The exact date is fundamentally unknown: for instance a person was last seen on some day and found death a few days later; or - We deal with future events so there is still some chance something goes wrong. I was wondering if there is a datatype to store such "dates" and how they are handed. It would result in thee-valued logic for some operations like for instance `date2 < 20001/01/01` should be `true`, `date2 < 2000/01/01` be `possible` and `date2 < 1998/01/01` should be `false`. If no such datatype is available, what are good practices to construct such "table" onself?