Memorizza tutte le date nel normale campo DATE nel database e dispone di un campo di accuratezza aggiuntivo sulla precisione del campo DATE.

date_created DATE,
date_created_accuracy INTEGER, 

data_created_accuracy: 1 = data esatta, 2 = mese, 3 = anno.

Se la data è sfocata (ad esempio, nel maggio 1980), memorizzarla all'inizio del periodo (ad esempio, il 1 ° maggio 1980). O se la data è precisa per anno (ad esempio 1980), memorizzarla come 1 gennaio. 1980 con il corrispondente valore di accuratezza.

In questo modo puoi facilmente eseguire una query in un modo un po 'naturale e avere ancora un'idea di come siano precise le date. Ad esempio questo ti permette di interrogare le date tra Jan 1st 1980 e Feb 28th 1981 , e ottenere date fuzzy 1980 e May 1980 .

Se non è necessario utilizzare questo tipo di dati come normali informazioni di data-ora, qualsiasi formato di stringa semplice farebbe.

Ma se hai bisogno di mantenere tutte le funzionalità, ci sono due soluzioni alternative a cui posso pensare, entrambe che richiedono ulteriori informazioni memorizzate nel database:

1. Crea campi min date e max date , che hanno valori diversi per i dati "incompleti", ma coincideranno per date precise.
2. Crea tipi per ogni tipo di data imprecisa (nessuno _ 0, data_missing _ 1, month_missing _ 2, year_missing_4, etc _ così puoi combinarli). Aggiungi un campo type ai record e mantieni le informazioni mancanti.

Questa è davvero più una definizione dei requisiti che un problema tecnico - ciò su cui devi concentrarti è "come possiamo definire le date nel passato" e la soluzione tecnica scorrerà.

Le volte in cui ho dovuto approcciare qualcosa del genere abbiamo in genere:

• Definire come mappare le cose - come suggerisce MichaelT, decidere che tutto ciò che è definito come Mese / Giorno viene definito come mezzanotte del 1 ° di detto mese. Questo è in genere abbastanza buono per la maggior parte degli scopi - se la data esatta fosse così importante probabilmente ne avresti il record 35 anni dopo, giusto?
• Scopri se hai bisogno di tracciare questo - IE, i record con date di creazione leggermente inventate hanno bisogno di una bandiera che lo dice? O è solo un problema di formazione degli utenti, quindi la gente sa e può agire di conseguenza.

A volte è necessario fare qualcosa di simile a rendere sfocate le date - ad esempio, potrebbe essere necessario che una data risponda a una query per qualsiasi cosa nel maggio 1978. Questo è fattibile - basta creare i campi create_date 2, vecchi record ottenere uno spread di 30 giorni, a seconda dei casi, i nuovi ottengono 2 valori identici.

Il modo più semplice per indicare se la data è accurata consiste nel creare un campo di precisione INT (1) con il valore NULL predefinito

Se la data è esatta data-ora del negozio in "date_created" & lasciare la precisione NULL

Se la data è esatta solo al mese, data-ora del negozio come 1o mese con valore di precisione 1

Se la data è accurata solo per data di inizio anno del negozio il 1 ° gennaio con valore di precisione 2

Puoi utilizzare numeri diversi per contenere valori diversi come il primo trimestre ecc.

In passato ho memorizzato le date con accuratezza come data di inizio e data di fine. Il giorno 21 maggio 2012 sarebbe rappresentato come inizio = 12 am, maggio 21,2012 e fine = 12 am, maggio 22,2012. L'anno 2012 sarebbe rappresentato come inizio = 12 am, gennaio1,2012 fine = 12 am, gennaio1013.

Non sono sicuro di raccomandare questo approccio. Quando si visualizzano le informazioni per l'utente, è necessario rilevare correttamente che un intervallo di date copre esattamente un giorno per mostrare "25 maggio" anziché due endpoint eccessivamente specifici (che significa gestire l'ora legale e così via).

Tuttavia, quando non si sta tentando di tradurre in umani, la programmazione con gli endpoint è molto più semplice rispetto alla precisione del centro +. Non si finisce con un sacco di casi. È carino.

Perché non memorizzare due date.

Created_After e Created_Before. La semantica attuale "creata prima o dopo" e "creata sopra o prima"

Quindi, se conosci la data esatta, Created_After e Created_Before saranno la stessa data.

Se sai che è stata la prima settimana di maggio 2000, quindi Created_After = '2000-05-01' e Created_Before = '2000-05-07'.

Se conosci solo maggio 1999, i valori saranno "1999-05-01" e "1999-05-30".

Se è "Summer of '42", i valori dovrebbero essere "1942-06-01" e "1942-08-31".

Questo schema è semplice da interrogare con SQL normale e abbastanza facile da seguire per un utente non tecnico.

Ad esempio, per trovare tutti i documenti che potrebbero essere stati creati nel maggio 2001:

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

Al contrario, per trovare tutti i documenti sicuramente creati nel maggio 2001:

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

Il formato di data ora ISO 8601 viene fornito con la definizione della durata, ad es.

2012-01-01P1M (leggi: 2012, 1 gennaio, periodo: 1 mese) è ciò che dovrebbe essere "nel gennaio 2012".

Lo userei per memorizzare i dati. Potrebbe essere necessario un campo di database di tipo String per farlo. È un argomento diverso su come condurre una ricerca ragionevole su questo.

Generalmente, li memorizzo ancora come date per le query generiche che sono ancora possibili anche se leggermente meno accurate.

Se è importante conoscere l'accuratezza che ho conservato in passato, una "finestra" di precisione può essere impostata come +/- decimale o come ricerca (giorno, mese, anno, ecc.). In altri casi invece della finestra, memorizzo il valore della data originale come stringa e converto quello che posso in un datetime, possibilmente 1978-05-01 00:00:00 e "Maggio 1978" per il tuo esempio dato.

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

dice chi? Ecco cosa fai:

1. Sono disponibili 3 colonne, Giorno, Mese, Anno, ciascuna di tipo int e una quarta colonna Tipo Data di Data.
2. Avere un trigger che utilizza le 3 colonne Day, Month, Year per creare TheDate se TheDate è lasciato null ma uno o più dei campi Day, Month, Year ha un valore.
3. Avere un trigger che compila i campi di giorno, mese, anno quando viene fornito l'orario, ma questi campi non lo sono.

Quindi se faccio un inserto come: insert into thistable (Day, Month, Year) values (-1, 2, 2012); allora TheDate diventerà il 2/1/2013 ma saprò che è davvero una data indeterminata nel 2/2012 a causa del -1 nel campo del giorno.

Se I insert into thistable (TheDate) values ('2/5/2012'); allora Day sarà 5, Month sarà 2, e Year sarà 2012 e perché nessuno di loro è -1 Saprò che questa è la data esatta.

Non perdo la possibilità di interrogare perché il trigger di inserimento / aggiornamento assicura che i miei 3 campi (Giorno, Mese, Anno) generino sempre un valore DateTime in TheDate che può essere interrogato.

Un'altra opzione sarebbe quella di memorizzare le date come numeri interi della forma YYYYMMDD .

• Sai solo che l'anno è il 1951: Salva come 19510000
• Sai che mese e anno sono marzo 1951: Salva come 19510300
• Sai che la data completa è il 14 marzo 1951: Salva come 19510314
• Una data completamente sconosciuta: archivia come 0

vantaggi

Puoi memorizzare la tua data fuzzy in un campo invece di due campi data o una data e una precisione come suggeriscono molte delle altre risposte.

Le query sono ancora semplici:

• tutti i record dell'anno 1951 - SELECT * FROM table WHERE thedate>=19510000 and thedate<19520000
• tutti i record di marzo 1951 - SELECT * FROM table where thedate>=19510300 and thedate<19510400
• tutti i record del 14 marzo 1951 - SELECT * FROM table where thedate=19510314

NOTE

• La tua GUI avrebbe bisogno di un GetDateString(int fuzzyDate) che sia abbastanza facile da implementare.
• L'ordinamento è facile con il formato int. Dovresti sapere che le date sconosciute verranno prima. Puoi invertirlo utilizzando 99 per "padding" anziché 00 per il mese o il giorno.

ISO 8601 specifica anche una sintassi per "date fuzzy". Il 12 febbraio 2012 alle 15:00 sarebbe "2012-02-12T15" e febbraio 2012 potrebbe essere semplicemente "2012-02". Questo si estende bene utilizzando l'ordinamento lessicografico standard:

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

Ecco la mia opinione su questo:

Vai da data fuzzy a oggetto datetime (che si adatta a un database)

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 poi una funzione che prende l'oggetto datetime e lo riporta in una data fuzzy.

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 poi un test unitario. Ho perso qualche 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'

C'è un caso d'angolo in cui un evento che si è verificato precisamente a 2001-01-01T00:00:00.333333 ma il sistema interpreterà come "2001", ma sembra molto improbabile.

Lavoro per una casa editrice che si occupa di molti vecchi libri in cui spesso non siamo in grado di ottenere le date esatte per le cose. In genere abbiamo due campi per una data data, la data e un circa booleano:

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

Usiamo il campo data per indicare la data di qualche evento, o una data che è "abbastanza vicina" nel caso in cui non conosciamo la data vera. Nel caso in cui non conosciamo la data esatta, contrassegniamo il campo dateCirca come Y e forniamo una data abbastanza vicina, contrassegnata come "1st", ad esempio

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

Panoramica

Ci sono molte possibili rappresentazioni, e quindi schemi di database, per la memorizzazione di date sfocate (o anche solo date sfocate):

1. Data-ora e codice che indicano la precisione o accuratezza
2. Data-ora e intervallo in cui esistono diverse possibilità per rappresentare un intervallo:
   1. Rappresenta tutti gli intervalli come quantità intera (o altra quantità numerica) di alcune unità fisse, ad es. giorni, minuti, nanosecondi.
   2. Rappresenta un intervallo sia come numero intero (o altra quantità numerica) che come codice che indica le sue unità.
3. Avvia e termina le date
4. Stringa
5. Distribuzione della probabilità:
   1. Quantità decimali o in virgola mobile per i parametri che specificano una distribuzione specifica in una particolare famiglia, ad es. media e deviazione standard di una distribuzione normale.
   2. Funzione di distribuzione della probabilità, ad es. come codice (di ricerca) (potenzialmente con parametri di valori specifici) o come espressione in un linguaggio, formato o rappresentazione sufficientemente espressivi.

[1], [2] e [3] sono tutti intervalli (implicitamente) uniformi, cioè un insieme di (ugualmente) possibili punti nel tempo.

[4] è il più espressivo, cioè quando si consente qualsiasi frase o frase (o almeno arbitrariamente lunga) scritta in linguaggio. Ma è anche il più difficile da lavorare. Nel limite, l'IA a livello umano sarebbe necessaria per gestire valori arbitrari. In pratica, l'intervallo di valori possibili dovrebbe essere limitato severamente e valori "strutturati" alternativi sarebbero probabilmente preferiti per molte operazioni, ad es. ordinamento, ricerca.

[5] è probabilmente la rappresentazione compact più generale che è (in qualche modo) pratica.

Intervalli uniformi

Gli intervalli uniformi sono il modo più semplice e compatto per rappresentare un insieme di (possibili) valori di data-ora.

Per [1], le parti del valore di data e ora vengono ignorate, cioè le parti corrispondenti a unità più fini della precisione o accuratezza indicate; altrimenti questo è equivalente a [2] e il codice di precisione / precisione è equivalente a un intervallo con le stesse unità (e una quantità implicita di 1).

[2] e [3] sono espressamente equivalenti. [1] è strettamente meno espressivo di entrambi, in quanto vi sono intervalli efficaci che non possono essere rappresentati da [1], es. una data di sfocatura equivalente a un intervallo di 12 ore che si estende su un limite di data.

[1] è più semplice da inserire per gli utenti rispetto a qualsiasi altra rappresentazione e in genere dovrebbe richiedere (almeno leggermente) una minore digitazione. Se i tempi di data possono essere inseriti in varie rappresentazioni di testo, ad es. "2013", "2014-3", "2015-5-2", "30/07/2016 11p", "31-07-2011 18:15", la precisione o la precisione potrebbero anche essere desunte automaticamente dall'input .

La precisione o precisione di [1] è anche la più semplice da convertire in un modulo da trasmettere agli utenti, ad es. "2015-5 con accuratezza mensile" a "Maggio 2015", contro "13 maggio 2015 2p, più o meno 13,5 giorni" (nota che quest'ultima non può essere rappresentata comunque da [1]).

stringhe

In pratica, i valori stringa dovranno essere convertiti in altre rappresentazioni per interrogare, ordinare o confrontare in altro modo più valori. Quindi, mentre qualsiasi linguaggio naturale (umano) scritto è strettamente più espressivo di [1], [2], [3] o [5], non abbiamo ancora i mezzi per gestire molto al di là delle rappresentazioni o dei formati standard di testo. Dato che, questa è probabilmente la rappresentazione meno utile di per sé .

Un vantaggio di questa rappresentazione è che i valori dovrebbero, in pratica, essere presentabili agli utenti così come sono e non richiedere che la trasformazione sia facilmente comprensibile.

Distribuzioni di probabilità

Le distribuzioni di probabilità generalizzano le rappresentazioni dell'intervallo uniforme [1], [2], [3] e (discutibilmente) sono equivalenti alla rappresentazione della stringa (generale) [4].

Un vantaggio delle distribuzioni di probabilità sulle stringhe è che il primo non è ambiguo.

[5-1] sarebbe appropriato per valori che (principalmente) si conformano a una distribuzione esistente, ad es. un valore di data-ora emesso da un dispositivo per il quale le misure sono note (o pensate) per conformarsi a una distribuzione specifica.

[5-2] è probabilmente il modo migliore (un po ') pratico per compattare rappresentare valori arbitrari "fuzzy datetime". Ovviamente la computabilità delle specifiche distribuzioni di probabilità usate è importante e ci sono problemi decisamente interessanti (e forse impossibili) da risolvere quando si interrogano, si ordinano o si confrontano valori diversi, ma molto probabilmente è già noto o risolto da qualche parte nell'attuale letteratura matematica e statistica quindi questo rappresenta sicuramente una rappresentazione estremamente generale e non ambigua.

Mi piace molto la soluzione di James Anderson - Limitare accuratamente le date è il modo per ottenere la struttura di query più flessibile . Un altro modo per ottenere lo stesso è usare un valore di inizio, fine o pari date più un interval (disponibile almeno in PostgreSQL , Oracle e SQLAlchemy ).

Nel tuo caso hai bisogno solo di anno, mese e giorno. L'anno e il mese sono obbligatori, il giorno è facoltativo. Userei qualcosa del genere:

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

Inoltre, puoi ancora utilizzare gli indici in modo molto efficace. Il (minuscolo = meno, le code ottengono un po ' più "complicato" (più lungo).

Vorrei semplicemente memorizzare l'ora esatta per le date normali e rendere generica la parte temporale della data fuzzy come 00:00:00. Poi farei tutte le date fuzzy il 1 ° del mese.

Quando esegui una query,

1. controlla gli intervalli di date in cui anche l'ora è uguale a 00:00:00 (fuzzy)
2. controlla gli intervalli di date in cui l'ora NON è uguale a 00:00:00 (reale)
3. controlla gli intervalli di date, ma ignora la porzione del tempo (combinata)

Esistono soluzioni migliori di questo, ma io personalmente odio i metadati (dati sui miei dati). Ha solo l'abitudine di sfuggire di mano dopo un po '.