Armazene todas as datas no campo DATE normal no banco de dados e tenha um campo de precisão adicional sobre como o campo DATE é realmente exato.

date_created DATE,
date_created_accuracy INTEGER, 

date_created_accuracy: 1 = data exata, 2 = mês, 3 = ano.

Se sua data for difusa (por exemplo, maio de 1980), armazene-a no início do período (por exemplo, 1º de maio de 1980). Ou, se a data estiver correta até o ano (por exemplo, 1980), armazene-a como 1º de janeiro. 1980 com valor de precisão correspondente.

Desta forma, pode facilmente consultar de uma forma um pouco natural e ainda ter noção de como as datas são precisas. Por exemplo, isso permite consultar datas entre Jan 1st 1980 e Feb 28th 1981 e obter datas difusas 1980 e May 1980 .

Se você não precisar usar esse tipo de dados como informações regulares de data e hora, qualquer formato de string simples funcionará.

Mas se você precisar manter toda a funcionalidade, há duas soluções alternativas nas quais posso pensar, ambas exigindo informações adicionais armazenadas no banco de dados:

1. Crie os campos min date e max date , que têm valores diferentes para dados "incompletos", mas coincidem para datas precisas.
2. Crie tipos para cada tipo de data imprecisa (nenhum _ 0, data_missing _1, month_missing_2, year_missing_4, etc _ para que você possa combiná-los). Adicione um campo type aos registros e mantenha as informações que estão faltando.

Isso é mais uma definição de requisitos do que um problema técnico - o que você precisa focar é "como podemos definir as datas no passado" e a solução técnica fluirá.

Nas vezes em que tive que abordar algo assim, normalmente:

• Defina como mapear coisas - como MichaelT sugere, decida que qualquer coisa que seja definida como Mês / Dia é definida como meia-noite no dia 1º do mês. Isso normalmente é bom o suficiente para a maioria dos propósitos - se a data exata fosse tão importante, você provavelmente teria um registro disso 35 anos depois, certo?
• Descobrir se você precisa acompanhar isso - ou seja, os registros com datas de criação levemente inventadas precisam de uma bandeira dizendo isso? Ou isso é apenas um problema de treinamento do usuário para que as pessoas saibam e possam agir de acordo.

Às vezes, é necessário fazer algo como tornar as datas difusas - por exemplo, que talvez uma data precise responder a uma consulta em maio de 1978. Isso é factível - basta criar seus dois campos create_date, registros antigos obter um spread de 30 dias, conforme apropriado, os novos recebem 2 valores idênticos.

A maneira mais simples de indicar se a data é precisa é criar um campo de precisão INT (1) com o padrão NULL

Se a data for exata, acesse a data e a hora em "date_created" & deixar precisão NULL

Se a data for exata apenas para o mês, armazene a data e a hora como 1º do mês com o valor de precisão 1

Se a data for precisa apenas para a data de armazenamento do ano em 1º de janeiro com valor de precisão 2

Você pode usar números diferentes para manter valores diferentes, como primeiro trimestre, etc.

No passado, armazenei datas-com-precisão como data de início e data de término. O dia 21 de maio de 2012 seria representado como início = 12 da manhã, 21 de maio de 2012 e final = 12 da manhã, de 22 de maio de 2012. O ano de 2012 seria representado como start = 12 am, Jan1, 2012 end = 12 am, Jan1, 2013.

Não tenho certeza se recomendaria essa abordagem. Ao exibir as informações para o usuário, você precisa detectar corretamente que um intervalo de datas cobre exatamente um dia para mostrar "25 de maio" em vez de dois pontos de extremidade mais específicos (o que significa lidar com o horário de verão e assim por diante). >

No entanto, quando você não está tentando traduzir para humanos, programar com os terminais é muito mais fácil do que com a precisão do centro +. Você não acaba com muitos casos. Isso é muito legal.

Por que não armazenar duas datas?

Created_After e Created_Before. A semântica real sendo "criada em ou depois de" e "criada em ou antes"

Se você souber a data exata, então Created_After e Created_Before serão a mesma data.

Se você sabe que foi a primeira semana de maio de 2000, então Created_After = '2000-05-01' e Created_Before = '2000-05-07'.

Se você acabou de conhecer maio de 1999, os valores serão '1999-05-01' e '1999-05-30'.

Se for "Summer of '42", os valores serão "1942-06-01" e "1942-08-31".

Esse esquema é simples de consultar com SQL normal e é muito fácil para um usuário não técnico seguir.

Por exemplo, para encontrar todos os documentos que podem foram criados em maio de 2001:

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

Por outro lado, para encontrar todos os documentos definitivamente criados em maio de 2001:

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

O formato de data e hora ISO 8601 é fornecido com a definição de duração, por exemplo

2012-01-01P1M (leia-se: 2012, 1º de janeiro, período: 1 mês) é o que deve ser “em janeiro de 2012”.

Eu usaria isso para armazenar os dados. Você pode precisar de um campo de banco de dados do tipo String para fazer isso. É um tópico diferente sobre como realizar uma pesquisa sensata sobre isso.

Geralmente, ainda os armazeno, já que as datas para um assunto de consulta geral ainda são possíveis, mesmo que sejam um pouco menos precisas.

Se é importante saber a precisão que eu tenho no passado, ou armazenei uma "janela" de precisão como um +/- decimal ou como uma pesquisa (dia, mês, ano, etc.). Em outros casos, em vez da janela, apenas armazeno o valor da data original como uma string e converto o que posso em um datetime, possivelmente 1978-05-01 00:00:00 e "May 1978" para o seu exemplo dado.

If you split it up into multiple columns, you lose the ability to query.

Diz quem? Veja o que você faz:

1. Tem 3 colunas, dia, mês, ano, cada tipo de int e uma quarta coluna TheDate of DateTime type.
2. Ter um acionador que usa as 3 colunas Day, Month, Year para criar TheDate se TheDate for deixado null, mas um ou mais dos campos Day, Month e Year tiver um valor.
3. Possui um acionador que preenche os campos Dia, Mês e Ano quando o TheDate é fornecido, mas esses campos não são.

Portanto, se eu fizer uma inserção como: insert into thistable (Day, Month, Year) values (-1, 2, 2012); , o TheDate se tornará 2/1/2013, mas saberei que é realmente uma data indeterminada em 2/2012 por causa do -1 no campo Day.

Se eu insert into thistable (TheDate) values ('2/5/2012'); , então Dia será 5, Mês será 2 e Ano será 2012 e, como nenhum deles é -1, eu sei que esta é a data exata.

Não perco a capacidade de consultar porque o gatilho de inserção / atualização garante que meus três campos (dia, mês, ano) sempre produzem um valor de DateTime em TheDate, que pode ser consultado.

Outra opção seria armazenar as datas como números inteiros do formulário YYYYMMDD .

• Você só sabe que o ano é 1951: Armazenar como 19510000
• Você sabe que o mês e o ano é março de 1951: loja como 19510300
• Você sabe que a data completa é 14 de março de 1951: Armazenar como 19510314
• Uma data completamente desconhecida: armazenar como 0

Benefícios

Você pode armazenar sua data difusa em um campo, em vez de dois campos de data ou uma data e uma precisão, como muitas outras respostas sugerem.

As consultas ainda são fáceis:

• todos os registros do ano de 1951 - SELECT * FROM table WHERE thedate>=19510000 and thedate<19520000
• todos os registros de março de 1951 - SELECT * FROM table where thedate>=19510300 and thedate<19510400
• todos os registros de 14 de março de 1951 - SELECT * FROM table where thedate=19510314

NOTAS

• Sua GUI precisaria de um GetDateString(int fuzzyDate) , o que é muito fácil de implementar.
• A classificação é fácil com o formato int. Você deve saber que datas desconhecidas virão primeiro. Você poderia inverter isso usando 99 para o "preenchimento" em vez de 00 para o mês ou o dia.

O ISO 8601 também especifica uma sintaxe para "datas difusas". 12 de fevereiro de 2012 às 15:00 seria "2012-02-12T15" e fevereiro de 2012 poderia ser simplesmente "2012-02". Isso se estende bem usando a classificação lexicográfica padrão:

$ (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

Aqui está minha opinião sobre isso:

Ir de data difusa para o objeto datetime (que caberá em um banco de dados)

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

E, em seguida, uma função que pega o objeto datetime e o move de volta para uma data difusa.

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()

E depois um teste de unidade. Eu perdi algum caso?

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'

Há um caso específico em que um evento ocorreu precisamente em 2001-01-01T00:00:00.333333 , mas o sistema interpretará como sendo apenas "2001", mas isso parece muito improvável.

Eu trabalho para uma editora que lida com muitos livros antigos nos quais muitas vezes não conseguimos as datas exatas para as coisas. Normalmente, temos dois campos para uma determinada entrada de data, a data e um circa booleano:

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

Usamos o campo de data para indicar a data de algum evento ou uma data "perto o suficiente", no caso de não sabermos a data verdadeira. No caso de não sabermos a data verdadeira, marcamos o campo dateCirca como Y e fornecemos uma data próxima o suficiente, que está marcada como "1ª", como

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

Visão geral

Existem muitas representações possíveis e, portanto, esquemas de banco de dados, para armazenar datas-tempos difusas (ou até mesmo datas difusas):

1. Data-hora e código indicando sua precisão ou exatidão
2. Data e hora e intervalo em que há várias possibilidades para representar um intervalo:
   1. Representa todos os intervalos como uma quantidade inteira (ou outra numérica) de alguma unidade fixa, por ex. dias, minutos, nanossegundos.
   2. Representa um intervalo como uma quantidade inteira (ou outra numérica) e um código indicando suas unidades.
3. Horários de início e término
4. String
5. Distribuição de probabilidade:
   1. Quantidades decimais ou de ponto flutuante para os parâmetros que especificam uma distribuição específica em uma família particular, por exemplo, média e desvio padrão de uma distribuição normal.
   2. Função de distribuição de probabilidade, por ex. como um código (de pesquisa) (potencialmente com parâmetros de valores específicos) ou como uma expressão em linguagem, formato ou representação suficientemente expressiva.

[1], [2] e [3] são todos intervalos (implicitamente) uniformes, isto é, um conjunto de pontos (igualmente) possíveis no tempo.

[4] é o mais expressivo, ou seja, ao permitir quaisquer frases ou frases de linguagem escrita possíveis (ou pelo menos arbitrariamente longas). Mas também é o mais difícil de se trabalhar. No limite, a AI de nível humano seria necessária para manipular valores arbitrários. Praticamente, a faixa de valores possíveis precisaria ser restringida severamente, e valores alternativos "estruturados" provavelmente seriam preferidos para muitas operações, por exemplo, ordenando, pesquisando.

[5] é provavelmente a representação mais geral compacta que é (um pouco) prática.

Intervalos uniformes

Intervalos uniformes são a forma compacta mais simples de representar um conjunto de valores (possíveis) de data e hora.

Para [1], partes do valor de data e hora são ignoradas, isto é, as partes correspondentes a unidades mais finas do que a precisão ou precisão indicadas; caso contrário, isso é equivalente a [2] e o código de precisão / precisão é equivalente a um intervalo com as mesmas unidades (e uma quantidade implícita de 1).

[2] e [3] são expressivamente equivalentes. [1] é estritamente menos expressivo do que ou porque existem intervalos efetivos que não podem ser representados por [1], ex. um fuzzy date-time equivalente a um intervalo de 12 horas que abrange um limite de data.

[1] é mais fácil para os usuários inserirem do que qualquer outra representação e geralmente deve exigir (pelo menos um pouco) menos digitação. Se os tempos de data podem ser inseridos em várias representações de texto, por ex. "2013", "2014-3", "2015-5-2", "30/07/2016 11p", "2016-07-31 18:15", a precisão ou precisão também pode ser deduzida automaticamente da entrada .

A exatidão ou precisão de [1] também é mais fácil de converter em um formulário a ser transmitido aos usuários, por exemplo, '2015-5 com precisão mensal' a 'maio de 2015', versus '13 de maio de 2015 2p, mais ou menos 13,5 dias' (apesar de que este último não pode ser representado por [1] de qualquer forma).

Cordas

Praticamente, os valores de sequência precisarão ser convertidos em outras representações para consulta, classificação ou comparação de vários valores. Portanto, embora qualquer linguagem natural (humana) escrita seja estritamente mais expressiva do que [1], [2], [3] ou [5], ainda não temos meios de lidar com muito além das representações ou formatos de texto padrão. Dado que, esta é provavelmente a representação menos útil por si mesma .

Uma vantagem dessa representação é que os valores devem, na prática, ser apresentados aos usuários como estão e não exigir que a transformação seja facilmente compreendida.

Distribuições de probabilidade

As distribuições de probabilidade generalizam as representações uniformes de intervalo [1], [2], [3] e (discutivelmente) são equivalentes à representação de string (geral) [4].

Uma vantagem das distribuições de probabilidade sobre strings é que a primeira não é ambígua.

[5-1] seria apropriado para valores que (principalmente) estão em conformidade com uma distribuição existente, por exemplo um valor de data e hora emitido por um dispositivo para o qual as medições são conhecidas (ou consideradas) para se adequar a uma distribuição específica.

[5-2] é provavelmente a melhor maneira (prática) de compactamente representar valores arbitrários de 'fuzzy datetime'. É claro que a computabilidade das distribuições de probabilidade específicas usadas importa e há definitivamente problemas interessantes (e talvez impossíveis) a serem resolvidos ao consultar, classificar ou comparar diferentes valores, mas muito disso provavelmente já é conhecido ou resolvido em algum lugar do existente. literatura matemática e estatística, então isso definitivamente se apresenta como uma representação extremamente geral e não ambígua.

Eu realmente gosto da solução de James Anderson - Vincular com precisão as datas é a maneira de obter a estrutura de consulta mais flexível . Outra maneira de conseguir o mesmo é usar um início, fim ou mesmo centro date mais interval (disponível pelo menos em PostgreSQL , Oracle e SQLAlchemy ).

No seu caso você precisa apenas de ano, mês e dia. Ano e mês são obrigatórios, o dia é opcional. Eu usaria algo assim:

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

Além disso, você ainda pode usar índices de forma muito eficaz. O (tiny = menos, queires obter um pouco mais "complicado" (mais).

Eu simplesmente armazenaria a hora exata para datas normais e faria a parte do tempo da data fuzzy genérica como 00:00:00. Eu então faria todas as datas difusas no primeiro dia do mês.

Quando você consulta, você

1. verifique os intervalos de datas em que a hora também é igual a 00:00:00 (difusa)
2. verifique os intervalos de datas em que a hora NÃO é igual a 00:00:00 (real)
3. verifique os intervalos de datas, mas ignore a parte da hora (combinada)

Existem soluções melhores do que isso, mas eu pessoalmente odeio metadados (dados sobre meus dados). Apenas tem o hábito de sair do controle depois de um tempo.