How to represent dates with uncertainty in PostgreSQL

Question

PostgreSQL provides a date type data type for storing dates. The problem with these dates, however, they cannot, as far as I know, cause uncertainty.

Sometimes no one knows the full date of something, but knows that it happened in January 1995, or in 1999 or 2000 ( date2 ). There may be several reasons:

• People do not remember the exact date;
• The exact date is completely unknown: for example, a person was last seen on some day and found death a few days later; or
• We are dealing with future events, so it is likely that something will go wrong.

I was wondering if there is a data type for storing such "dates" and how they are transmitted. This will lead to logical logic for some operations, such as, for example, date2 < 20001/01/01 should be true , date2 < 2000/01/01 be possible and date2 < 1998/01/01 should be false .

If this data type is not available, what are some good methods for creating such a “table”?

Answer 1

There are several different ways to approach fuzzy dates. In PostgreSQL you can use

• a couple of date columns (early_user_building, last_data_ landing),
• date column and precision column ('2012-01-01', 'year') or
• a data type of a range range (daterange) or
• a varchar ('2013-01-2?', '2013 - ?? - 05') or
• another table or tables with any of these data types.

The range data type is common to the latest versions of PostgreSQL. You can use the rest in any SQL files.

The type of fuzziness you need depends on the application. How you request fuzzy dates depends on what type or structure of data you choose. You need to firmly understand what types of fuzziness you need to store, and the questions that your users need. And you need to check so that your database can answer their questions.

For example, in legal systems, dates may be poorly stored or damaged. Someone might say: “It was some Thursday in January 2014. I know that it was Thursday because it was a day of garbage collection,” or “It was the first week in June or July of last year.” To record this fuzziness, you need another table.

Or the postmark may be clouded so that you can only read "14, 2014". You know that it was mailed on the 14th, but you don’t know what month. Again, you need another table.

Some (all?) Of them will not give you three-valued logic if you do not jump over some hoops. ("Maybe" is not a valid boolean value.)

Answer 2

To add to Mike's post, I would use date comments, such as:

 date Comment ------------------------------------------------------------------- 1/1/2010 Sometime in 2010 7/8/2014 Customer says they will pay the second week in July 1/1/2015 Package will arrive sometime next year in January 

In addition, you can use date details. Create a separate column for Year, Month, and Day. What was never known, leave it blank.