Almacene todas las fechas en el campo FECHA normal en la base de datos y tenga un campo de precisión adicional sobre la precisión del campo FECHA.

date_created DATE,
date_created_accuracy INTEGER, 

date_created_accuracy: 1 = fecha exacta, 2 = mes, 3 = año.

Si su fecha es borrosa (por ejemplo, mayo de 1980), guárdela al inicio del período (por ejemplo, el 1 de mayo de 1980). O si su fecha es precisa para el año (por ejemplo, 1980), guárdela como el 1 de enero. 1980 con el valor de precisión correspondiente.

De esta manera, puede realizar consultas fácilmente de una manera un tanto natural y aún así tener una idea de cuán precisas son las fechas. Por ejemplo, esto le permite consultar fechas entre Jan 1st 1980 y Feb 28th 1981 , y obtener fechas difusas 1980 y May 1980 .

Si no necesita usar este tipo de datos como información de fecha y hora regular, cualquier formato de cadena simple sería suficiente.

Pero si necesita mantener toda la funcionalidad, hay dos soluciones que se me ocurren, ambas requieren información adicional almacenada en la base de datos:

1. Cree los campos min date y max date , que tienen valores diferentes para los datos "incompletos", pero coincidirán para fechas precisas.
2. Cree tipos para cada tipo de fecha inexacta (ninguno _ 0, fecha_ desaparición _ 1, mes_ desaparecido _ 2, año_missing_4, etc _ para que pueda combinarlos). Agregue un campo type a los registros y mantenga la información que falta.

En realidad, se trata más de una definición de requisitos que de un problema técnico. En lo que debe centrarse es en "cómo podemos definir las fechas en el pasado" y la solución técnica fluirá.

Las veces que he tenido que abordar algo como esto, por lo general hemos:

• Defina cómo mapear las cosas, como sugiere MichaelT, decida que todo lo que se define como Mes / Día se defina como medianoche el 1 de cada mes. Por lo general, esto es lo suficientemente bueno para la mayoría de los propósitos: si la fecha exacta fuera tan importante, probablemente tendría un registro 35 años después, ¿no?
• Averigüe si necesita hacer un seguimiento de esto: IE, ¿los registros con fechas ligeramente creadas necesitan una marca que lo indique? O es que solo es un problema de capacitación del usuario para que la gente lo sepa y pueda actuar en consecuencia.

A veces, uno necesita hacer algo como hacer que las fechas sean confusas; por ejemplo, es posible que una fecha tenga que responder a una consulta para cualquier cosa en mayo de 1978. Esto es factible: simplemente haga sus campos create_date 2, registros antiguos obtener un margen de 30 días según corresponda, los nuevos obtienen 2 valores idénticos.

La forma más sencilla de indicar si la fecha es precisa es crear un campo de precisión INT (1) con NULL predeterminado

Si la fecha es precisa, almacene la fecha y la hora en "date_created" & dejar exactitud NULA

Si la fecha solo es precisa para el mes, fecha y hora de la tienda como primer día del mes con un valor de precisión 1

Si la fecha solo es precisa para el año, fecha y hora de la tienda, el 1 de enero con el valor de precisión 2

Puede usar diferentes números para mantener diferentes valores, como el primer trimestre, etc.

En el pasado, he almacenado fechas con precisión como fecha de inicio y fecha de finalización. El día 21 de mayo de 2012 se representaría como inicio = 12 am, mayo 21, 2012 y final = 12 am, 22 de mayo de 2012. El año 2012 se representaría como inicio = 12 a.m.Jan1, 2012 final = 12 a.m.Jan1,2013.

No estoy seguro si recomendaría este enfoque. Al mostrar la información al usuario, debe detectar correctamente que un rango de fechas cubre exactamente un día para mostrar "25 de mayo" en lugar de dos puntos finales sobreespecíficos (lo que significa tratar con el horario de verano, etc.).

Sin embargo, cuando no está intentando traducir a humano, la programación con los puntos finales es mucho más fácil que con el centro + precisión. No terminas con muchos casos. Eso es bastante bueno.

Por qué no almacenar dos fechas.

Created_After y Created_Before. La semántica real se está "creando en o después de" y "se creó en o antes de"

Entonces, si sabes la fecha exacta, Created_After y Created_Before serán la misma fecha.

Si sabe que fue la primera semana de mayo de 2000, Created_After = '2000-05-01' y Created_Before = '2000-05-07'.

Si solo sabe mayo de 1999, los valores serán '1999-05-01' y '1999-05-30'.

Si es "Verano de '42", los valores serían '1942-06-01' y '1942-08-31'.

Este esquema es fácil de consultar con SQL normal y bastante fácil de seguir para un usuario no técnico.

Por ejemplo, para encontrar todos los documentos que podría se han creado en mayo de 2001:

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

A la inversa, para encontrar todos los documentos que fueron definitivamente creados en mayo de 2001:

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

ISO 8601 el formato de fecha y hora viene con la definición de la duración, por ejemplo,

2012-01-01P1M (leído: 2012, 1 de enero, período: 1 mes) es lo que debería ser "en enero de 2012".

Utilizaría esto para almacenar los datos. Es posible que necesite un campo de base de datos de tipo Cadena para hacerlo. Es un tema diferente sobre cómo realizar una búsqueda sensata sobre eso.

En general, todavía las guardo como fechas para el negocio de consultas generales, aunque es un poco menos preciso.

Si es importante saber la precisión que he almacenado en el pasado, una "ventana" de precisión ya sea como +/- decimal o como búsqueda (día, mes, año, etc.). En otros casos, en lugar de la ventana, solo almaceno el valor de fecha original como una cadena y convierto lo que puedo en una fecha, posiblemente 1978-05-01 00:00:00 y "Mayo de 1978" para su ejemplo dado.

  

Si lo divide en varias columnas, pierde la capacidad de   consulta.

Dice quién? Esto es lo que haces:

1. Tiene 3 columnas, Día, Mes, Año, cada tipo de tipo int, y una cuarta columna, el tipo Fecha de fecha y hora.
2. Tenga un activador que use las 3 columnas Día, Mes, Año para compilar TheDate si TheDate se deja en blanco, pero uno o más de los campos Day, Month, Year tienen un valor.
3. Tenga un activador que rellene los campos Día, Mes, Año cuando se suministre TheDate, pero estos campos no.

Entonces, si hago una inserción como: insert into thistable (Day, Month, Year) values (-1, 2, 2012); , TheDate se convertirá en 2/1/2013 pero sabré que es una fecha indeterminada en 2/2012 debido al -1 en el campo Día.

Si I insert into thistable (TheDate) values ('2/5/2012'); , el día será 5, el mes será 2 y el año será 2012 y como ninguno de ellos es -1, sabré que esta es la fecha exacta.

No pierdo la capacidad de realizar consultas porque el activador de inserción / actualización se asegura de que mis 3 campos (Día, Mes, Año) siempre produzcan un valor DateTime en TheDate que se puede consultar.

Otra opción sería almacenar las fechas como enteros de la forma YYYYMMDD .

• Solo sabes que el año es 1951: Almacenar como 19510000
• Ya sabes que el mes y el año es marzo de 1951: Almacenar como 19510300
• Sabes que la fecha completa es el 14 de marzo de 1951: Almacenar como 19510314
• Una fecha completamente desconocida: Almacenar como 0

Beneficios

Puede almacenar su fecha difusa en un campo en lugar de dos campos de fecha o una fecha y una precisión, como sugieren muchas de las otras respuestas.

Las consultas siguen siendo fáciles:

• todos los registros del año 1951 - SELECT * FROM table WHERE thedate>=19510000 and thedate<19520000
• todos los registros de marzo de 1951 - SELECT * FROM table where thedate>=19510300 and thedate<19510400
• todos los registros del 14 de marzo de 1951 - SELECT * FROM table where thedate=19510314

NOTAS

• Su GUI necesitaría un GetDateString(int fuzzyDate) que es bastante fácil de implementar.
• Ordenar es fácil con el formato int. Debes saber que las fechas desconocidas serán lo primero. Puede revertir esto utilizando 99 para el 'relleno' en lugar de 00 para el mes o día.

ISO 8601 también especifica una sintaxis para "fechas difusas". El 12 de febrero de 2012 a las 3 p.m. sería "2012-02-12T15" y febrero de 2012 podría ser simplemente "2012-02". Esto se extiende muy bien utilizando la clasificación lexicográfica estándar:

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

Aquí está mi opinión sobre esto:

Ir de fecha difusa a objeto de fecha y hora (que se ajustará a una base de datos)

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

Y luego una función que toma el objeto datetime y lo mueve de nuevo a una fecha borrosa.

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

Y luego una prueba de unidad. ¿Me perdí algún 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'

Hay un caso de esquina en el que un evento que ocurrió precisamente en 2001-01-01T00:00:00.333333 pero el sistema interpretará como solo "2001", pero parece muy poco probable.

Trabajo para una empresa editorial que comercializa muchos libros antiguos en los que a menudo no podemos obtener las fechas exactas de las cosas. Normalmente tenemos dos campos para una entrada de fecha determinada, la fecha y un circa boolean:

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

Usamos el campo de fecha para indicar la fecha de algún evento, o una fecha que es "lo suficientemente cercana" en el caso de que no sepamos la fecha verdadera. En el caso de que no sepamos la fecha verdadera, marcamos el campo dateCirca como Y y damos una fecha lo suficientemente cercana, que está marcada como la "primera", 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

Descripción general

Hay muchas representaciones posibles, y por lo tanto esquemas de base de datos, para almacenar fechas y horarios difusos (o incluso solo fechas difusas):

1. Fecha-hora y código que indica su precisión o exactitud
2. Fecha-hora e intervalo donde hay varias posibilidades para representar un intervalo:
   1. Representa todos los intervalos como una cantidad entera (u otra numérica) de alguna unidad fija, por ejemplo. días, minutos, nanosegundos.
   2. Representa un intervalo como una cantidad entera (u otra numérica) y un código que indica sus unidades.
3. Fecha y hora de inicio
4. cadena
5. Distribución de probabilidad:
   1. Cantidades decimales o en coma flotante para los parámetros que especifican una distribución específica en una familia en particular, por ejemplo. media y desviación estándar de una distribución normal.
   2. función de distribución de probabilidad, por ejemplo, como un código (de búsqueda) (potencialmente con parámetros de valores específicos), o como una expresión en un lenguaje, formato o representación suficientemente expresivo.

[1], [2] y [3] son todos intervalos (implícitos) uniformes, es decir, un conjunto de (igualmente) posibles puntos en el tiempo.

[4] es el más expresivo, es decir, cuando se permiten las posibles oraciones o frases escritas en el lenguaje (o al menos arbitrariamente largas). Pero también es el más difícil de trabajar. En el límite, se requeriría la inteligencia artificial a nivel humano para manejar valores arbitrarios. En la práctica, el rango de valores posibles tendría que restringirse severamente, y probablemente se preferirían valores 'estructurados' alternativos para muchas operaciones, por ejemplo. clasificación, búsqueda.

[5] es probablemente la representación compacta más general que es (algo) práctica.

Intervalos uniformes

Los intervalos uniformes son la forma compacta más simple de representar un conjunto de (posibles) valores de fecha y hora.

Para [1], las partes del valor de fecha y hora se ignoran, es decir, las partes correspondientes a unidades más finas que la precisión o exactitud indicada; de lo contrario, esto es equivalente a [2] y el código de precisión / precisión es equivalente a un intervalo con las mismas unidades (y una cantidad implícita de 1).

[2] y [3] son expresivamente equivalentes. [1] es estrictamente menos expresivo que cualquiera de los dos, ya que hay intervalos efectivos que no pueden representarse por [1], ej. una fecha y hora difusa equivalente a un intervalo de 12 horas que abarca un límite de fecha.

[1] es más fácil para los usuarios ingresar que cualquier otra representación y, en general, debería requerir (al menos un poco) menos escritura. Si las fechas se pueden ingresar en varias representaciones de texto, p. Ej. "2013", "2014-3", "2015-5-2", "30/07/2016 11p", "2016-07-31 18:15", la precisión o exactitud también se podría inferir automáticamente de la entrada .

La precisión o precisión de [1] también es más fácil de convertir a un formulario para ser transmitido a los usuarios, por ejemplo. '2015-5 con mes de precisión' hasta "mayo de 2015", en comparación con "13 de mayo de 2015 2p, más o menos 13.5 días" (aunque, en cualquier caso, esta última no puede ser representada por [1]).

Cadenas

En la práctica, los valores de cadena deberán convertirse a otras representaciones para consultar, ordenar o comparar de otro modo varios valores. Entonces, si bien cualquier lenguaje natural (humano) escrito es estrictamente más expresivo que [1], [2], [3] o [5], aún no tenemos los medios para manejar mucho más allá de las representaciones o formatos de texto estándar. Dado esto, esta es probablemente la representación menos útil por sí misma .

Una ventaja de esta representación es que los valores deberían, en la práctica, estar presentes para los usuarios tal como están y no requerir que la transformación sea fácilmente comprensible.

Distribuciones de probabilidad

Las distribuciones de probabilidad generalizan las representaciones de intervalo uniforme [1], [2], [3] y (posiblemente) son equivalentes a la representación de cadena (general) [4].

Una de las ventajas de las distribuciones de probabilidad sobre las cadenas es que la primera no es ambigua.

[5-1] sería apropiado para los valores que (en su mayoría) se ajustan a una distribución existente, p. ej. una salida de valor de fecha y hora de un dispositivo para el cual se sabe (o se piensa) que las mediciones se ajustan a una distribución específica.

[5-2] es probablemente la mejor manera (algo) práctica de compactamente representar valores arbitrarios de "fecha y hora difusos". Por supuesto, la computabilidad de las distribuciones de probabilidad específicas utilizadas es importante y definitivamente hay problemas interesantes (y quizás imposibles) que deben resolverse al consultar, clasificar o comparar valores diferentes, pero es probable que gran parte de esto ya se conozca o se resuelva en algún lugar de la actual. Literatura matemática y estadística, por lo que definitivamente es una representación extremadamente general y no ambigua.

Realmente me gusta la solución de James Anderson : delimitar con precisión las fechas es la forma de obtener la estructura de consulta más flexible . Otra forma de lograr lo mismo es usar un inicio, un final o incluso un centro date más un interval (disponible al menos en PostgreSQL , Oracle y SQLAlchemy ).

En su caso solo necesita año, mes y día. Se requiere año y mes, día es opcional. Yo usaría algo así:

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

Además, aún puedes usar índices de manera muy efectiva. El (diminuto = menos, las colas se vuelven un poco más "complicadas" (más largas).

Simplemente almacenaría la hora exacta para las fechas normales y haría que la parte de la fecha difusa genérica como 00:00:00. Luego haría todas las fechas difusas el 1 de cada mes.

Cuando usted consulta, usted

1. compruebe los intervalos de fechas donde la hora también es igual a 00:00:00 (difusa)
2. compruebe los intervalos de fechas en los que la hora NO es igual a 00:00:00 (real)
3. comprueba los intervalos de fechas pero ignora la parte de tiempo (combinada)

Hay mejores soluciones que esta, pero personalmente odio los metadatos (datos sobre mis datos). Simplemente tiene el hábito de salirse de las manos después de un tiempo.