To jest problem, na który wpadłem kilka razy. Wyobraź sobie, że masz rekord, który chcesz zapisać w tabeli bazy danych. Ta tabela ma kolumnę DateTime o nazwie „data_tworzona”. Ten jeden rekord został utworzony dawno temu i nie jesteś pewien dokładnej daty, ale znasz rok i miesiąc. Inne rekordy, które znasz tylko rok. Inne rekordy, które znasz dzień, miesiąc i rok.

Nie możesz użyć pola DateTime, ponieważ „maj 1978” nie jest prawidłową datą. Jeśli podzielisz go na wiele kolumn, utracisz możliwość zapytania. Czy ktoś jeszcze na to wpadł, jeśli tak, jak sobie z tym poradziłeś?

Aby wyjaśnić system, który buduję, jest to system, który śledzi archiwa. Niektóre treści zostały wyprodukowane dawno temu, a wszystko, co wiemy, to „maj 1978 r.”. Mógłbym zapisać go jako 1 maja 1978 r., Ale tylko w pewien sposób, aby wskazać, że ta data jest dokładna tylko w stosunku do miesiąca. W ten sposób kilka lat później, gdy odzyskuję to archiwum, nie jestem zdezorientowany, gdy daty się nie zgadzają.

Dla moich celów ważne jest, aby odróżnić „nieznany dzień maja 1978 r.” Od „1 maja 1978 r.”. Ponadto nie chciałbym przechowywać niewiadomych jako 0, np. „0 maja 1978 r.”, Ponieważ większość systemów baz danych odrzuca tę wartość jako niepoprawną datę.

Przechowuj wszystkie daty w normalnym polu DATA w bazie danych i miej dodatkowe pole dokładności, jak dokładne jest rzeczywiście pole DATA.

date_created DATE,
date_created_accuracy INTEGER, 

date_created_accuracy: 1 = dokładna data, 2 = miesiąc, 3 = rok.

Jeśli twoja data jest rozmyta (np. Maj 1980), zapisz ją na początku okresu (np. 1 maja 1980). Lub jeśli data jest zgodna z rokiem (np. 1980), zapisz ją jako 1 stycznia. 1980 z odpowiednią wartością dokładności.

W ten sposób można łatwo zapytać w nieco naturalny sposób i nadal mieć pojęcie o dokładności dat. Na przykład pozwala to wyszukiwać daty między Jan 1st 1980i Feb 28th 1981, a także uzyskiwać rozmyte daty 1980i May 1980.

Jeśli nie musisz używać tego rodzaju danych jako zwykłej informacji o dacie i czasie, wystarczy prosty format ciągu.

Ale jeśli chcesz zachować całą funkcjonalność, mogę wymyślić dwa obejścia, oba wymagające dodatkowych informacji przechowywanych w bazie danych:

1. Utwórz min datei max datepola, które mają różne wartości dla „niekompletnych” danych, ale będą zbieżne dla dokładnych dat.
2. Utwórz typy dla każdego rodzaju niedokładnej daty (brak _ 0, brak daty _ 1, brak miesiąca _ 2, brak daty_4 itd., Aby je połączyć). Dodaj typepole do rekordów i zachowaj brakujące informacje.

Jest to raczej definicja wymagań niż problem techniczny - na czym należy się skupić, „jak możemy zdefiniować daty w przeszłości”, a rozwiązanie techniczne będzie działać.

Czasy, kiedy musiałem podejść do czegoś takiego, zwykle:

• Zdefiniuj, jak mapować rzeczy - jak sugeruje MichaelT , zdecyduj, że wszystko, co jest zdefiniowane jako Miesiąc / Dzień, zostanie zdefiniowane jako północ pierwszego dnia tego miesiąca. Zazwyczaj jest to wystarczające do większości celów - gdyby dokładna data była tak ważna, prawdopodobnie zapisałbyś ją 35 lat później, prawda?
• Zastanów się, czy musisz to wyśledzić - IE, czy rekordy z lekko wymyślonymi datami utworzenia wymagają flagi z informacją? Czy jest to tylko kwestia szkolenia użytkowników, aby ludzie wiedzieli i mogli odpowiednio postępować.

Czasami trzeba zrobić coś takiego, aby rozmyć daty - na przykład, że może być potrzebna odpowiedź na zapytanie o cokolwiek w maju 1978 roku. Jest to wykonalne - wystarczy, że twoje pola create_date 2, stare rekordy otrzymają 30 rozłożone dni odpowiednio, nowe otrzymują 2 identyczne wartości.

Najprostszym sposobem na określenie, czy data jest dokładna, jest utworzenie pola dokładności INT (1) z domyślną wartością NULL

Jeśli data jest dokładna, zapisz datę i godzinę w polu „data_tworzona” i pozostaw dokładność NULL

Jeśli data jest dokładna tylko w stosunku do miesiąca, zapisz datę i godzinę jako 1. miesiąca z wartością dokładności 1

Jeśli data jest dokładna tylko dla roku, należy zapisać datę i godzinę 1 stycznia z dokładnością 2

Możesz użyć różnych liczb do przechowywania różnych wartości, takich jak pierwszy kwartał itp

W przeszłości zapisywałem daty z dokładnością jako datę początkową i końcową. Dzień 21, 2012 może być reprezentowany jako początek = 12 rano, 211,2012, a koniec = 12 rano, 222,2012. Rok 2012 byłby reprezentowany jako początek = 12 rano, 1 stycznia 2012 r. Koniec = 12 rano, 1 stycznia 2013 r.

Nie jestem pewien, czy poleciłbym to podejście. Podczas wyświetlania informacji użytkownikowi należy poprawnie wykryć, że zakres dat dokładnie obejmuje dzień, aby pokazać „może 25” zamiast dwóch nadmiernie określonych punktów końcowych (co oznacza radzenie sobie z oszczędnościami światła dziennego i tak dalej).

Jednak gdy nie próbujesz tłumaczyć na człowieka, programowanie z punktami końcowymi jest o wiele łatwiejsze niż z dokładnością do środka +. Nie kończy się na wielu przypadkach. To całkiem miłe.

Dlaczego nie przechowywać dwóch dat.

Created_After i Created_Before. Rzeczywista semantyka jest „tworzona w lub po” i „tworzona w lub przed”

Więc jeśli znasz dokładną datę, wówczas Created_After i Created_Before będą tą samą datą.

Jeśli wiesz, że był to pierwszy tydzień maja 2000 r., Wówczas Created_After = „2000-05-01” i Created_Before = „2000-05-07”.

Jeśli znasz tylko maj 1999 r., Wartościami będą „1999-05-01” i „1999-05-30”.

Jeśli jest to „Lato roku 42”, wówczas wartości to „1942-06-01” i „1942-08-31”.

Ten schemat jest prosty do zapytania za pomocą normalnego SQL i dość łatwy do naśladowania przez nietechnicznego użytkownika.

Na przykład, aby znaleźć wszystkie dokumenty, które mogły zostać utworzone w maju 2001 r .:

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

I odwrotnie, aby znaleźć wszystkie dokumenty, które zostały definitywnie utworzone w maju 2001 r .:

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

Format daty i godziny ISO 8601 zawiera definicję czasu trwania, np

2012-01-01P1M (czytaj: 2012, 1 stycznia, okres: 1 miesiąc) to, co powinno być „w styczniu 2012”.

Użyłbym tego do przechowywania danych. W tym celu może być potrzebne pole bazy danych typu String. To inny temat, jak przeprowadzić rozsądne poszukiwanie w tej sprawie.

Generalnie nadal przechowuję je, ponieważ nadal jest możliwe generowanie ogólnych zapytań, nawet jeśli są nieco mniej dokładne.

Jeśli ważne jest, aby znać dokładność, którą miałem w przeszłości, albo zapisywałem „okno” dokładności jako dziesiętne +/- lub jako odnośnik (dzień, miesiąc, rok itp.). W innych przypadkach zamiast okna po prostu przechowuję oryginalną wartość daty jako ciąg znaków i przekształcam, co mogę, w datę i godzinę, być może 1978-05-01 00:00:00 i „Maj 1978” dla podanego przykładu.

Jeśli podzielisz go na wiele kolumn, utracisz możliwość zapytania.

Mówi kto? Oto co robisz:

1. Posiadają 3 kolumny, Dzień, Miesiąc, Rok, każdy typu int i czwartą kolumnę TheDate typu DateTime.
2. Wyzwalacz, który używa 3 kolumn Dzień, Miesiąc, Rok do zbudowania TheDate, jeśli TheDate ma wartość null, ale jedno lub więcej pól Dzień, Miesiąc, Rok ma wartość.
3. Mają wyzwalacz, który wypełnia pola Dzień, Miesiąc, Rok, gdy podano TheDate, ale te pola nie są.

Więc jeśli zrobię wstawkę w stylu: insert into thistable (Day, Month, Year) values (-1, 2, 2012);wtedy TheDate stanie się 2/1/2013, ale będę wiedział, że to naprawdę nieokreślona data w 2/2012 z powodu -1 w polu Dzień.

Jeśli ja, insert into thistable (TheDate) values ('2/5/2012');to Dzień będzie 5, Miesiąc będzie 2, a Rok będzie 2012, a ponieważ żaden z nich nie ma -1, będę wiedział, że to dokładna data.

Nie tracę możliwości zapytania, ponieważ wyzwalacz wstawiania / aktualizacji zapewnia, że ​​moje 3 pola (Dzień, Miesiąc, Rok) zawsze generują wartość DateTime w TheDate, którą można zapytać.

Inną opcją byłoby przechowywanie dat jako liczb całkowitych formularza YYYYMMDD.

• Wiesz tylko, że jest rok 1951: Store as 19510000
• Wiesz, że miesiącem i rokiem jest marzec 1951: Store as 19510300
• Wiesz, że pełna data to 14 marca 1951: Store as 19510314
• Zupełnie nieznana data: zapisz jako 0

Korzyści

Możesz przechowywać swoją rozmytą datę w jednym polu zamiast dwóch pól daty lub daty i dokładności, jak sugeruje wiele innych odpowiedzi.

Zapytania są nadal łatwe:

• wszystkie rekordy za rok 1951 - SELECT * FROM table WHERE thedate>=19510000 and thedate<19520000
• wszystkie rekordy za marzec 1951 r. - SELECT * FROM table where thedate>=19510300 and thedate<19510400
• wszystkie rekordy z 14 marca 1951 r. - SELECT * FROM table where thedate=19510314

UWAGI

• Twój GUI potrzebuje GetDateString(int fuzzyDate)łatwego do wdrożenia.
• Sortowanie jest łatwe dzięki formatowi int. Powinieneś wiedzieć, że nieznane daty będą najważniejsze. Możesz to odwrócić, używając 99„wypełnienia” zamiast 00miesiąca lub dnia.

ISO 8601 określa również składnię dla „rozmytych dat”. 12 lutego 2012 r. O godzinie 15:00 będzie to „2012-02-12T15”, a luty 2012 r. Może być po prostu „2012-02”. To ładnie rozszerza się przy użyciu standardowego sortowania leksykograficznego:

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

Oto moje zdanie na ten temat:

Przejdź od daty rozmytej do obiektu datetime (który zmieści się w bazie danych)

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

Następnie funkcja, która pobiera obiekt datetime i przenosi go z powrotem do niewyraźnej daty.

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

A potem test jednostkowy. Czy przegapiłem jakieś przypadki?

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'

Istnieje przypadek narożny, w którym wydarzenie, które dokładnie miało miejsce, 2001-01-01T00:00:00.333333ale system zinterpretuje to jako „2001”, ale wydaje się to bardzo mało prawdopodobne.

Pracuję dla firmy wydawniczej, która zajmuje się wieloma starymi książkami, w których często nie możemy uzyskać dokładnych dat rzeczy. Zwykle mamy dwa pola dla danej daty, daty i około wartości logicznej:

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

Używamy pola daty, aby wskazać datę jakiegoś zdarzenia lub datę, która jest „wystarczająco bliska” w przypadku, gdy nie znamy prawdziwej daty. W przypadku, gdy nie znamy prawdziwej daty, oznaczamy dateCircapole jako Yi podajemy wystarczająco dokładną datę, która jest oznaczona jako „pierwsza”, na przykład

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

Przegląd

Istnieje wiele możliwych reprezentacji, a tym samym schematów baz danych, do przechowywania rozmytych dat i godzin (lub nawet tylko rozmytych dat):

1. Data i godzina oraz kod wskazujący jego precyzję lub dokładność
2. Data i godzina oraz przedział, w przypadku których istnieje kilka możliwości przedstawienia przedziału:
   1. Reprezentuj wszystkie przedziały jako liczbę całkowitą (lub inną liczbę) pewnej ustalonej jednostki, np. Dni, minuty, nanosekundy.
   2. Przedział reprezentuje zarówno liczbę całkowitą (lub inną liczbę), jak i kod wskazujący jej jednostki.
3. Data rozpoczęcia i zakończenia
4. Strunowy
5. Rozkład prawdopodobieństwa:
   1. Ilości dziesiętne lub zmiennoprzecinkowe dla parametrów określających konkretny rozkład w określonej rodzinie, np. Średnia i odchylenie standardowe rozkładu normalnego.
   2. Funkcja rozkładu prawdopodobieństwa, np. Jako (przeglądowy) kod (potencjalnie z parametrami określonych wartości) lub jako wyrażenie w wystarczająco ekspresyjnym języku, formacie lub reprezentacji.

[1], [2] i [3] to wszystkie (domyślnie) jednolite przedziały, tj. Zbiór (jednakowo) możliwych punktów w czasie.

[4] jest najbardziej wyrazisty, tzn. Gdy dopuszcza wszelkie możliwe (lub przynajmniej arbitralnie długie) pisane zdania lub frazy językowe. Ale najtrudniej jest z tym pracować. W tym limicie sztuczna inteligencja na poziomie ludzkim byłaby wymagana do obsługi dowolnych wartości. W praktyce zakres możliwych wartości musiałby zostać poważnie ograniczony, a alternatywne wartości „ustrukturyzowane” byłyby prawdopodobnie preferowane dla wielu operacji, np. Sortowania, wyszukiwania.

[5] jest prawdopodobnie najbardziej ogólną zwartą reprezentacją, która jest (nieco) praktyczna.

Jednolite interwały

Jednolite interwały to najprostszy zwarty sposób reprezentowania zestawu (możliwych) wartości daty i godziny.

W przypadku [1] części wartości daty i godziny są ignorowane, tj. Części odpowiadające jednostkom drobniejszym niż wskazana precyzja lub dokładność; w przeciwnym razie jest to równoważne z [2], a kod dokładności / dokładności jest równoważny interwałowi z tymi samymi jednostkami (i implikowaną ilością 1).

[2] i [3] są wyraźnie równoważne. [1] jest ściśle mniej wyrazisty niż oba, ponieważ istnieją skuteczne odstępy czasu, których nie można przedstawić za pomocą [1], np. rozmyta data-czas odpowiadająca 12 godzinnemu przedziałowi, który obejmuje granicę daty.

[1] jest łatwiejszy dla użytkowników do wprowadzania danych niż jakakolwiek inna reprezentacja i na ogół powinien wymagać (przynajmniej nieznacznie) mniej pisania. Jeśli daty mogą być wprowadzane w różnych reprezentacjach tekstowych, np. „2013”, „2014-3”, „2015-5-2”, „7/30/2016 11p”, „2016-07-31 18:15” , precyzję lub dokładność można również wywnioskować automatycznie na podstawie danych wejściowych.

Dokładność lub precyzję [1] można również najłatwiej przekonwertować na formularz, który należy przekazać użytkownikom, np. „2015-5 z miesięczną dokładnością” na „maj 2015”, w porównaniu z „13 maja 2015 2p, plus minus 13,5 dni” (zauważ jednak, że ten ostatni i tak nie może być reprezentowany przez [1]).

Smyczki

W praktyce wartości ciągów będą musiały zostać przekonwertowane na inne reprezentacje w celu tworzenia zapytań, sortowania lub porównywania wielu wartości w inny sposób. Tak więc, chociaż każdy pisany naturalny (ludzki) język jest bardziej wyrazisty niż [1], [2], [3] lub [5], nie mamy jeszcze możliwości radzenia sobie znacznie poza standardowymi reprezentacjami tekstowymi lub formatami. Biorąc to pod uwagę, jest to prawdopodobnie najmniej przydatna reprezentacja sama w sobie .

Jedną z zalet tej reprezentacji jest to, że wartości powinny w praktyce być prezentowane użytkownikom w obecnej postaci i nie wymagają transformacji, aby były łatwo zrozumiałe.

Rozkłady prawdopodobieństwa

Rozkłady prawdopodobieństwa uogólniają jednolite reprezentacje przedziałów [1], [2], [3] i (prawdopodobnie) są równoważne (ogólnej) reprezentacji ciągu [4].

Jedną z zalet rozkładów prawdopodobieństwa nad łańcuchami jest to, że te pierwsze są jednoznaczne.

[5-1] byłoby odpowiednie dla wartości, które (głównie) są zgodne z istniejącym rozkładem, np. Wyjście wartości daty i czasu z urządzenia, dla którego wiadomo (lub uważa się), że pomiary są zgodne z określonym rozkładem.

[5-2] jest prawdopodobnie najlepszym (nieco) praktycznym sposobem kompaktowego przedstawienia arbitralnych wartości „rozmytej daty / godziny”. Oczywiście obliczalność użytych rozkładów prawdopodobieństwa ma znaczenie i są zdecydowanie interesujące (i być może niemożliwe) problemy do rozwiązania przy wyszukiwaniu, sortowaniu lub porównywaniu różnych wartości, ale wiele z nich jest już prawdopodobnie znanych lub rozwiązanych gdzieś w istniejących literatura matematyczna i statystyczna, więc jest to zdecydowanie bardzo ogólna i niejednoznaczna reprezentacja.

Naprawdę podoba mi się rozwiązanie Jamesa Andersona - precyzyjne ograniczenie dat jest sposobem na uzyskanie najbardziej elastycznej struktury zapytań. Innym sposobem osiągnięcia tego samego jest użycie początku, końca lub nawet centrum dateplus interval(dostępne przynajmniej w PostgreSQL , Oracle i SQLAlchemy ).

W twoim przypadku potrzebujesz tylko roku, miesiąca i dnia. Wymagany jest rok i miesiąc, dzień jest opcjonalny. Użyłbym czegoś takiego:

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

Plus jest to, że nadal możesz bardzo skutecznie używać indeksów. (Małe = minus, kolejki stają się nieco bardziej „skomplikowane” (dłużej).

Po prostu zapisałbym dokładny czas dla normalnych dat i uczyniłby część czasu rozmytą datą ogólną jak 00:00:00. Wszystkie niewyraźne daty uczyniłbym wtedy pierwszym dniem miesiąca.

Kiedy pytasz, ty

1. sprawdź zakresy dat, w których czas jest również równy 00:00:00 (rozmyte)
2. sprawdź zakresy dat, w których czas NIE jest równy 00:00:00 (rzeczywisty)
3. sprawdź zakresy dat, ale zignoruj ​​część czasu (łącznie)

Są lepsze rozwiązania niż to, ale osobiście nienawidzę metadanych (danych o moich danych). Ma zwyczaj po prostu wymknąć się z rąk po chwili.