这是我遇到过几次的问题。假设您有一条要存储到数据库表中的记录。该表具有一个名为“ date_created”的DateTime列。这项特定记录创建于很久以前,您不确定确切的日期,但知道年份和月份。您只知道年份的其他记录。您还知道日期,月份和年份的其他记录。

您不能使用DateTime字段,因为“ 1978年5月”不是有效的日期。如果将其分成多列,则会失去查询能力。还有其他人遇到过这个问题吗,如果可以的话,您是如何处理的呢?

为了弄清我正在构建的系统,它是一个跟踪档案的系统。一些内容是很久以前制作的,我们所知道的只是“ 1978年5月”。我可以将其存储为1978年5月1日,但是只能以某种方式表示该日期仅对月份准确。这样几年后,当我检索该存档时,当日期不匹配时,我就不会感到困惑。

对我而言,区分“ 1978年5月的未知日期”很重要”和“ 1978年5月1日”。另外,我也不想将未知数存储为0,例如“ 1978年5月0日”,因为大多数数据库系统都会拒绝将其作为无效的日期值。

评论

将“ 1978年5月的未知日期”与“ 1978年5月1日”区分开来很重要吗?

@MichaelT:是的,区别很重要。

martinfowler.com/eaaDev/TimePoint.html

@aslum:大多数数据库系统都将其视为无效的日期值

@JimmyHoffa-您从未遇到过模糊的日期场景,或者您需要比较日期的场景吗?无论哪种情况,都有一个共同的病史:您记得去年4月1日进行了阑尾切除术,但是1975年的某个时候进行了扁桃体切除术,而在某年的5月和6月发生了其他事情。如果您想知道某些医学事件是在其他医学突破之前还是之后发生的,该怎么办?这是在他们检查艾滋病毒的血液供应之前或之后发生的吗?

#1 楼

将所有日期存储在数据库的正常DATE字段中,并具有其他准确性字段,该字段实际的准确性是多少。

date_created DATE,
date_created_accuracy INTEGER, 


date_created_accuracy:1 =准确的日期,2 =月,3 =年。

如果您的日期模糊(例如1980年5月)在时段开始时将其存储(例如1980年5月1日)。或者,如果您的日期准确到年份(例如1980年),则将其存储为1月1日。 1980年,具有相应的精度值。

这种方法可以轻松地以某种自然的方式进行查询,并且仍然对日期的准确性有所了解。例如,这允许您查询介于Jan 1st 1980Feb 28th 1981之间的日期,并获取模糊日期1980May 1980

评论


您仍然必须根据我所看到的在此处计算日期结束时间,因此我认为在查询之间很难做到,因为您最多只能选择一个计算字段。

–怀亚特·巴内特(Wyatt Barnett)
13年4月8日在19:56

好的答案,真的很聪明。从mytable中选择*,其中date_created在“ 1980/1/1”和“ 1981/2/28”之间,且date_created_accuracy <= 2;。天才。

– Naftuli Kay
13年4月8日在21:21

我鼓励您将日期精确度视为“天”。如果确切的日期是0,则可以使用更灵活的日期“夏天的某个时候”,该日期的准确度是从6月1日开始的90天,而不是硬编码的特定日期范围。它还可以处理多年的准确性。

–user40980
13年4月8日在21:45

您可能应该将其作为答案提交,MichaelT

– Supr
13年4月9日在9:45

+1:此解决方案的另一个好处是,您可以根据date_created_accuracy字段的值添加显示逻辑。您可以在结果或用户界面中显示“ 1980年5月”,也可以仅显示“ 1980”,前提是该字段所显示的准确度很高。

– Kyralessa
15年6月26日在15:31

#2 楼

如果您不需要将此类数据用作常规日期时间信息,则可以使用任何简单的字符串格式。

但是如果您需要保留所有功能,则可以想到两种解决方法,都需要在数据库中存储其他信息:


创建min datemax date字段,它们对于“不完整”数据具有不同的值,但对于准确的日期而言将是一致的。
为每种不正确的日期创建类型(无_ 0,date_missing _ 1,month_missing _ 2,year_missing_4等等,因此您可以将它们组合在一起)。在记录中添加一个type字段,并保留丢失的信息。


评论


最小和最大日期字段也是我的第一个想法。

–迈克尔·伊佐(Michael Itzoe)
2013年4月8日15:30



很久以前,我们不得不解决完全相同的问题。用户可以讲述过去任何时间发生的事件的故事,因此我们必须支持模糊日期。经过反复的讨论,我们得出的解决方案与此处的superM建议最相似,其中日期存储为包含故事日期的最小和最大可能瞬间。报告日期时,可以从最小日期和最大日期之间的差异中提取准确性(即“此记录对月/年/日是准确的”)。无需存储第三字段以确保准确性。

–meetamit
13年4月8日在16:47

最小日期和最大日期字段为+1。我认为这是最灵活,最精确且易于使用的解决方案。

– Supr
13年4月9日在11:13

起初我反对这个想法。但我认为这是最灵活的方法。

–阿努拉格·卡利亚(Anurag Kalia)
13年4月9日在11:47

这是自然的。您不是在描述模糊的日期,而是在描述一个时间范围.....它有一个开始和一个结束。

– Pieter B
2013年4月9日14:53

#3 楼

实际上,这实际上是一个需求定义,而不是技术问题,您需要关注的是“我们如何定义过去的日期”,技术解决方案将不断发展。

我们不得不采用通常的方法:


定义方法,如MichaelT建议的那样,确定将定义为“月/日”的任何内容定义为午夜在所述月份的1号。通常对于大多数用途来说已经足够了-如果确切的日期是如此重要,那么您可能会在35年后记录下来,对吧?
弄清楚是否需要跟踪-IE,请使用稍微编造的创建日期需要标记说吗?还是仅仅是一个用户培训问题,以便人们知道并可以采取相应的行动。

有时候,您需要做一些使日期变得模糊的事情,例如,某个日期可能需要响应一个日期。 1978年5月查询任何东西。这是可行的-只需将您的create_date 2个字段设置为适当,旧记录将适当地分散30天,新记录将获得2个相同的值。

评论


+1-我正在用双重约会方法来制定答案。您的答案首先到达这里。

–user40980
13年4月8日在15:29

+1,这很丑陋,并且为不需要它的新条目创建了很多无用的额外信息,但另一方面,它的确使查询变得比以前简单得多。一段时间以来,我们一直在使用类似的解决方案来解决相关问题。

–伊兹卡塔
13年4月8日在17:05

@Izkata-公平的观点,但是当您需要制作一个应该在一个月的时间范围内的单一观点时,您可以得到多大的优雅。当然,它比在某个地方即时计算查询的开始和结束要漂亮。

–怀亚特·巴内特(Wyatt Barnett)
13年4月8日在19:58

+1表示能够表示任意粒度而不会出现枚举值的爆炸式增长。

–丹在火光中摆弄
13年4月8日在20:56

#4 楼

表示日期是否准确的最简单方法是创建一个默认值为NULL的准确度字段INT(1)。

如果日期准确,则将日期时间存储在“ date_created”中并保留准确度NULL

如果日期仅精确到月份存储日期时间到1月1日,且精度值是1

如果日期仅精确到年份存储日期时间到1月1日,并且精度值是1 2

您可以使用不同的数字来保存不同的值,例如第一季度等

评论


当您这样做时,查询就会变得非常冗长。

– Blfl
13年4月8日在16:34

这对于不属于干净月份边界的数据(例如“ Q2 1991”和“ Winter 1978-1979”)有困难。

–user40980
13年4月8日在17:37

OP需要某种方式来表示该日期仅对月份准确。

– David Strachan
13年4月8日在18:32

您在此处滥用NULL的含义。 NULL表示“未知”,因此,如果日期正确,则精度不能为NULL。它可以是“ 1”。

–科尼拉克
13年4月9日在10:57

@Konerak语义上是。但是,由于大多数日期都是准确的,因此仅需要识别特殊情况,并在此处使用NULL作为默认值。

– David Strachan
13年4月9日在12:20

#5 楼

过去,我已经将准确的日期存储为开始日期和结束日期。 2012年5月21日这一天将表示为开始= 2012年5月21日上午12点,结束= 2012年5月22日上午12点。 2012年将被表示为2012年1月1日开始= 2012年1月1日结束,2013年1月1日结束。

我不确定是否会推荐这种方法。在向用户显示信息时,您需要正确地检测到日期范围恰好覆盖了一天,以便显示“ may 25”而不是两个过度指定的端点(这意味着要处理夏令时等等)。

但是,当您不打算翻译成人类时,使用端点编程比使用中心+准确性要容易得多。您最终不会遇到很多情况。很好。

评论


实际上,如果范围始终存储为UTC,那么确定如何显示范围并不需要那么棘手。作为UTC时间戳,每天,每周,每月,每年-甚至季节和季度-都将有两个恒定的,全局的,不同的且易于确定的数字,代表该时期的开始和结束。逻辑简单地变成一些if语句,以查看两个日期是否在某种类型的周期的开始和结束。无需复杂的数学或时区内容:)

– Supr
13年4月9日在11:50

@Supr确定特定的秒是否在特定的人类时期的边界上,这本身就是一个难题。尤其是从长远来看,随着地球自转速度减慢,人类对当地时间的定义也将发生不小的变化。

– Craig Gidney
2013年4月9日在16:34



#6 楼

为什么不存储两个日期。

Created_After和Created_Before。实际的语义是“在……上或之后创建的”和“在……上或之前创建的”

,因此,如果您知道确切的日期,则Created_After和Created_Before将是相同的日期。

如果您知道这是2000年5月的第一周,则Created_After ='2000-05-01'和Created_Before ='2000-05-07'。

如果您只知道1999年5月,则值将为分别为“ 1999-05-01”和“ 1999-05-30”。

如果它是“ 42的夏天”,则值分别为“ 1942-06-01”和“ 1942-08” -31'。

使用一般的SQL可以很容易地查询此模式,对于非技术用户来说也很容易遵循。

例如查找所有可能包含以下内容的文档创建于2001年5月:

SELECT * FROM DOCTAB WHERE Created_After < '2001-05-31' And Created_Before > 2001-05-01;


相反地,查找所有肯定是2001年5月创建的文档:

SELECT * FROM DOCTAB WHERE Created_After > '2001-05-01' And Created_Before < 2001-05-31;


评论


我认为这是最优雅的解决方案。

– Pieter B
13年4月9日在11:20

这与superM和Strilanc的答案相同。 +1不过是为了更清楚地说明并显示查询的简单程度。

– Supr
2013年4月9日在12:08

#7 楼

ISO 8601日期时间格式带有持续时间定义,例如,

2012-01-01P1M(读取:2012年1月1日,期限:1个月)应为“ 2012年1月”。

我将使用它来存储数据。为此,您可能需要一个String类型的数据库字段。如何对此进行明智的搜索是另一个主题。

评论


+1表示创意,但-1表示不使用日期字段,原因是搜索和/或查找的原因

–mmmmmm
2013年4月9日14:43在

取决于数据库。但是,这可能是扩展的基础,但问题是:如果您搜索的是这种情况下的文档,那么结果集中的文档是否比1月12日更新?这不是小事。在这里,问题是如何存储模糊日期。

–马特西亚斯·朗格(Matthias Ronge)
13年4月9日在14:49

#8 楼

通常,我仍然将它们存储为日期,以使一般查询的业务仍然可能,即使准确性稍差一些。

如果重要的是要知道我过去的精度,可以将精度“窗口”存储为+/-十进制或查找(天,月,年等)。在其他情况下(而不是在窗口中),我只是将原始日期值存储为字符串,然后将我可以转换为日期时间的格式转换为给定示例,可能是1978-05-01 00:00:00和“ 1978年5月”。 >

#9 楼


如果将其分成多列,则会失去查询的能力。


说谁?您的操作如下:


具有3列,分别是int类型的Day,Month,Year和DateTime类型的第四列TheDate。
如果TheDate保留为空,则具有使用三列Day,Month,Year来构建TheDate的触发器,但是Day,Month,Year字段中的一个或多个具有值。
具有填充触发器的触发器提供TheDate时的Day,Month,Year字段,但没有提供。

因此,如果我执行以下插入操作:insert into thistable (Day, Month, Year) values (-1, 2, 2012);,那么TheDate将成为2013年2月1日,但我知道它确实是不确定的由于Day字段中的-1,因此日期在2/2012中。

如果我insert into thistable (TheDate) values ('2/5/2012');,则Day将是5,Month将是2,Year将是2012,并且因为它们都不是-1我会知道这是确切的日期。

我不会失去查询的能力,因为插入/更新触发器可确保我的3个字段(日,月,年)始终在其中生成DateTime值。可以查询的日期。

#10 楼

另一种选择是将日期存储为YYYYMMDD形式的整数。

您仅知道年份是1951:存储为19510000

您知道月份和年份是三月1951年:存储为19510300

您知道完整的日期是1951年3月14日:存储为19510314

一个完全未知的日期:存储为0

>
好处
您可以将一个模糊的日期存储在一个字段中,而不是两个日期字段,也可以将日期和准确性存储在其他答案中所建议的。
查询仍然很容易:

1951年的所有记录-SELECT * FROM table WHERE thedate>=19510000 and thedate<19520000

1951年3月的所有记录-SELECT * FROM table where thedate>=19510300 and thedate<19510400

1951年3月14日的所有记录-SELECT * FROM table where thedate=19510314


注释

您的GUI需要一个易于实现的GetDateString(int fuzzyDate)
使用int格式进行排序很容易。您应该知道未知的日期将首先出现。您可以使用99来代替“ padding”,而不是月份或天来使用00


评论


您如何表示“ 1941-1942年冬季”的模糊日期?可能是1941年12月或1942年1月。

–user40980
2014年8月8日14:43



您的问题与一般解决方案有关。原始问题并未将此列为问题。根据发布的问题,有时会知道完整的日期,有时只知道年份和月份,有时甚至是年份。没有提到模糊日期范围的问题。我同意如果您需要解决此问题,则需要两个日期(尽管将范围存储为两个“模糊日期整数”可能比存储两个“硬”日期更具灵活性)。

–里克
2014年9月9日15:30



#11 楼

ISO 8601还指定了“模糊日期”的语法。 2012年2月12日下午3点将是“ 2012-02-12T15”,而2012年2月可能只是“ 2012-02”。使用标准字典分类法可以很好地扩展此功能:

$ (echo "2013-03"; echo "2013-03"; echo "2012-02-12T15"; echo "2012-02"; echo "2011") | sort
2011
2012
2012-02
2012-02-12T15
2013-03


#12 楼

这是我的看法:

从模糊日期转到日期时间对象(可以放入数据库中)

import datetime
import iso8601

def fuzzy_to_datetime(fuzzy):
    flen = len(fuzzy)
    if flen == 4 and fuzzy.isdigit():
        dt = datetime.datetime(year=int(fuzzy), month=1, day=1, microsecond=111111)

    elif flen == 7:
        y, m = fuzzy.split('-')
        dt = datetime.datetime(year=int(y), month=int(m), day=1, microsecond=222222)

    elif flen == 10:
        y, m, d = fuzzy.split('-')
        dt = datetime.datetime(year=int(y), month=int(m), day=int(d), microsecond=333333)

    elif flen >= 19:
        dt = iso8601.parse_date(fuzzy)

    else:
        raise ValueError("Unable to parse fuzzy date: %s" % fuzzy)

    return dt


然后函数,它使用datetime对象,并将其移回模糊的日期。

def datetime_to_fuzzy(dt):
    ms = str(dt.microsecond)
    flag1 = ms == '111111'
    flag2 = ms == '222222'
    flag3 = ms == '333333'

    is_first = dt.day == 1
    is_jan1 = dt.month == 1 and is_first

    if flag1 and is_jan1:
        return str(dt.year)

    if flag2 and is_first:
        return dt.strftime("%Y-%m")

    if flag3:
        return dt.strftime("%Y-%m-%d")

    return dt.isoformat()


然后进行单元测试。我错过了任何情况吗?

if __name__ == '__main__':
    assert fuzzy_to_datetime('2001').isoformat() == '2001-01-01T00:00:00.111111'
    assert fuzzy_to_datetime('1981-05').isoformat() == '1981-05-01T00:00:00.222222'
    assert fuzzy_to_datetime('2012-02-04').isoformat() == '2012-02-04T00:00:00.333333'
    assert fuzzy_to_datetime('2010-11-11T03:12:03Z').isoformat() == '2010-11-11T03:12:03+00:00'

    exact = datetime.datetime(year=2001, month=1, day=1, microsecond=231)
    assert datetime_to_fuzzy(exact) == exact.isoformat()

    assert datetime_to_fuzzy(datetime.datetime(year=2001, month=1, day=1, microsecond=111111)) == '2001'
    assert datetime_to_fuzzy(datetime.datetime(year=2001, month=3, day=1, microsecond=222222)) == '2001-03'
    assert datetime_to_fuzzy(datetime.datetime(year=2001, month=6, day=6, microsecond=333333)) == '2001-06-06'

    assert datetime_to_fuzzy(fuzzy_to_datetime('2002')) == '2002'
    assert datetime_to_fuzzy(fuzzy_to_datetime('2002-05')) == '2002-05'
    assert datetime_to_fuzzy(fuzzy_to_datetime('2002-02-13')) == '2002-02-13'
    assert datetime_to_fuzzy(fuzzy_to_datetime('2010-11-11T03:12:03.293856+00:00')) == '2010-11-11T03:12:03.293856+00:00'


在一个极端的情况下,一个事件恰好发生在2001-01-01T00:00:00.333333处,但系统将其解释为只是“ 2001”,但是似乎不太可能。

#13 楼

我在一家出版公司工作,该公司处理很多旧书,而我们常常不知道确切的日期。对于给定的日期条目,我们通常有两个字段,即日期和一个大约布尔值:

date date
dateCirca enum('Y', 'N')


我们使用日期字段来表示某个事件的日期,或者在我们不知道真实日期的情况下,“足够接近”的日期。如果我们不知道真实的日期,则将dateCirca字段标记为Y并给出足够接近的日期,将其标记为“ 1st”,例如

1st March, 2013  // We don't know the day of the month
1st January, 2013  // We don't know the month/day of the year
1st January, 2000  // We don't know the month/day/year, we only know the century


#14 楼

概述

有许多可能的表示形式,以及数据库模式,用于存储模糊日期时间(甚至只是模糊日期):


日期时间和指示其精度或准确性的代码
日期和时间间隔,其中有几种表示时间间隔的可能性:


将所有时间间隔表示为整数的整数(或其他数值)一些固定单位,例如天,分钟,纳秒。
将时间间隔表示为整数(或其他数字)数量和表示其单位的代码。


开始和结束日期时间
字符串
概率分布:


用于指定特定族中特定分布的参数的小数或浮点数正态分布的均值和标准差。
概率分布函数,例如作为(查找)代码(可能带有特定值的参数),或作为具有充分表现力的语言,格式或表示形式的表达式。



[1], [2]和[3]都是(隐含的)统一间隔,即一组(相等)可能的时间点。

[4]是最具有表现力的,即在允许任何可能的时间( (或至少任意长)的书面语言句子或短语。但这也是最难处理的。在极限情况下,将需要人类级别的AI处理任意值。实际上,可能的值的范围将需要严格地限制,并且对于许多操作,例如,对于许多操作,替代的“结构化”值可能是优选的。排序,搜索。

[5]可能是(有点)实用的最通用的紧凑表示。

均匀间隔

均匀间隔是表示一组(可能)日期时间值的最简单的紧凑方法。

对于[1],日期时间值的部分将被忽略,即对应于比所指示的精度或准确度更精细的单位的部分;否则,它等效于[2],并且精度/精度代码等效于具有相同单位的间隔(隐含量为1)。

[2]和[3]在表达上等效。严格来说,[1]的表达力不及任何一种,因为存在无法用[1]表示的有效间隔。

[1]比其他表示形式更易于用户输入,并且通常要求(至少稍微)减少键入次数。如果日期时间可以用各种文本表示形式输入,例如“ 2013”​​,“ 2014-3”,“ 2015-5-2”,“ 7/30/2016 11p”,“ 2016-07-31 18:15”,也可以从输入中自动推断出精度或准确性。

[1]的精度也最容易转换为要传达给用户的形式,例如“ 2015-5的月精度”为“ 2015年5月”,而“ 2015年5月13日2p,正负13.5天”(请注意,后者无论如何都不能用[1]表示)。

字符串

实际上,需要将字符串值转换为其他表示形式,以便查询,排序或比较多个值。因此,尽管任何书面(人类)自然语言都比[1],[2],[3]或[5]更具表现力,但我们还没有办法处理超出标准文本表示形式或格式的内容。鉴于此,它本身可能是最没有用的表示形式。

这种表示形式的优点之一是,实际上,值应该可以按原样呈现给用户,并且不需要转换就易于理解。 。

概率分布

概率分布概括了统一间隔表示[1],[2],[3],并且(可以说)等效于(通用)字符串表示[4]。

相对于字符串,概率分布的一个优势是前者是明确的。

[5-1]对于(大多数)符合现有分布的值(例如,从已知(或认为)测量结果符合特定分布的设备输出的日期时间值。

[5-2]可能是紧凑表示的最佳(某种程度上)实用方法任意“模糊日期时间”值。当然,所使用的特定概率分布的可计算性很重要,并且在查询,排序或比较不同的值时肯定要解决一些有趣的(也许是不可能的)问题,但是其中很多可能已经为人所知或已解决。数学和统计文献,因此这绝对代表了一种极为笼统和明确的表述。

#15 楼

我真的很喜欢James Anderson的解决方案-准确界定日期是获得最灵活的查询结构的方法。实现此目的的另一种方法是使用开始,结束甚至中心date加上interval(至少在PostgreSQL,Oracle和SQLAlchemy中可用)。

#16 楼

在您的情况下,您只需要年,月和日。年份和月份是必填项,日期是可选的。我会使用类似的东西:

year smallint not null,
month smallint not null,
day smallint


此外,您仍然可以非常有效地使用索引。 (小=减,查询变得“复杂”(更长)。

评论


但这意味着,如果模糊性也吞噬了月度部分,则此方法将失败。

–阿努拉格·卡利亚(Anurag Kalia)
13年4月9日在11:49

@AnuragKalia-因此使month字段为空。没有理由以后无法重新配置。

– JeffO
2013年4月9日15:10

那只是一个例子。解决方案必须足够通用以适应将来的问题。如果您指定的范围是2013年3月15日至2013年3月22日,则此方法无效。上面的最小-最大答案是最通用的答案。

–阿努拉格·卡利亚(Anurag Kalia)
13年4月9日在18:27

您是否在OP帖子中找到了这样的要求,或者仅仅是您的幻想?

–努比亚水手
13年4月10日在4:29

将月份设置为可空值可让您指定日期,但不能指定月份。也没有道理。何时是1978-??-31?

–MSalters
13年7月16日在11:53

#17 楼

我将简单地存储正常日期的确切时间,并使模糊日期的时间部分通用,例如00:00:00。然后,我会将所有模糊日期设为该月的1号。

查询时,您


检查时间范围也等于的日期范围00:00:00(模糊)
检查时间不等于00:00:00(真实)的日期范围
检查日期范围,但忽略时间部分(组合)

有比这更好的解决方案,但是我个人讨厌元数据(有关我的数据的数据)。它只是有一段时间后会失控的习惯。

评论


这将如何处理具有时间00:00:00的真实日期?

– gna
13年4月9日在9:46

从理论上讲,可以在该时间添加一个实际日期,但不会发生。我见过具有数百万行的表,但其中没有一个表的datetime值时间为00:00:00。实用主义胜于常规。

– Kenpachi上尉
13年4月9日在10:05