本文是小编为大家收集整理的关于如何在PostgreSQL中表示具有不确定性的日期的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
PostgreSQL提供date格式数据类型来存储日期.但是，这些日期的问题是，据我所知，他们不能 - 关于不确定性的原因.
有时人们不知道某事的全日期，但知道它发生在1995年1月或" 1999或2000"(date2).可能有几个原因:
- 人们不记得确切的日期;
- 确切的日期从根本上是未知的:例如，一个人最后一天是一个人，几天后发现死亡；或
- 我们处理未来的事件，因此仍然有一些可能出现问题.
我想知道是否有数据类型可以存储这样的"日期"以及它们的递交方式.对于某些操作，例如date2 < 20001/01/01应该为true，date2 < 2000/01/01 be possible和date2 < 1998/01/01应该为false. >
如果没有这样的数据类型，那么在自己上构造这样的"表"的好实践是什么?
推荐答案
有几种不同的方法来处理模糊日期.在PostgreSQL中，您可以使用
- 一对日期列(Ermiest_possible_date，lestest_possible_date)，
- 日期列和精密列('2012-01-01'，'Year')或
- a 范围数据类型(deTaterange)或
- varchar('2013-01-2?''，'2013 - ?? - 05')或
- 其他任何数据类型的表或表格.
范围数据类型是最新版本的PostgreSQL的特殊之处.您可以在任何SQL DBMS中使用其他.
您需要的模糊性是应用程序依赖性的.查询模糊日期的方式取决于您选择的数据类型或结构.您需要坚定地掌握需要存储哪种模糊性以及用户需要回答的问题的类型.您需要测试以确保您的数据库可以回答他们的问题.
例如，在法律体系中，日期可能会被记住不足或污损.有人可能会说:"这是2014年1月的某个星期四. ".要记录这种模糊性，您需要另一个表.
或邮戳可能会损坏，因此您只能阅读" 14，2014".您知道它是在14日邮戳的，但是您不知道哪个月.同样，您需要另一个表.
除非您跳过一些篮球，否则其中的一些(全部?)不会给您三个值的逻辑. ("可能"不是有效的布尔值.)
其他推荐答案
要添加Mike发布的内容，我将使用日期评论，例如:
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
另外，您可以使用日期零件.创建一个单独的专栏，用于一年，月和一天.在未知中的任何事物都空白.
问题描述
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?
推荐答案
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 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
Also, you can use date parts. Create a separate column for the Year, Month, and Day. What ever in unknown leave it blank.
更多相关问答
联系站长