Preferowany sposób przechowywania DateTime

Informacje o dacie i godzinie możemy przechowywać na kilka sposobów. Jakie jest najlepsze podejście do przechowywania informacji DateTime?

Przechowywanie daty i godziny w 2 osobnych kolumnach lub jednej kolumnie przy użyciu DateTime ?

Czy możesz wyjaśnić, dlaczego to podejście jest lepsze?

^{(Link do dokumentacji MySQL w celach informacyjnych, pytanie jest ogólne, nie dotyczy MySQL)
Typy daty i godziny : Data i godzina}

Przechowywanie danych w jednej kolumnie jest preferowanym sposobem, ponieważ są one nierozerwalnie połączone. Punkt w czasie to jedna informacja, a nie dwie.

Powszechnym sposobem przechowywania danych daty / godziny, stosowanym „za kulisami” przez wiele produktów, jest konwersja ich na wartość dziesiętną, gdzie „data” jest całkowitą częścią wartości dziesiętnej, a „czas” jest ułamkiem wartość. Tak więc 1900-01-01 00:00:00 jest przechowywany jako 0,0, a 20 września 2016 09:34:00 jest przechowywany jako 42631.39861. 42631 to liczba dni od 1900-01-01. .39861 to część czasu, która upłynęła od północy. Nie używaj do tego bezpośrednio typu dziesiętnego, użyj jawnego typu daty / godziny; mój punkt tutaj jest tylko ilustracją.

Przechowywanie danych w dwóch osobnych kolumnach oznacza, że ​​będziesz musiał połączyć obie wartości kolumn za każdym razem, gdy chcesz sprawdzić, czy dany punkt w czasie jest wcześniejszy czy późniejszy niż zapisana wartość.

Jeśli przechowujesz wartości osobno, niezmiennie napotkasz „trudne” błędy, które są trudne do wykrycia. Weźmy na przykład:

IF OBJECT_ID('tempdb..#DT') IS NOT NULL
DROP TABLE #DT;
CREATE TABLE #DT
(
    dt_value DATETIME NOT NULL
    , d_value DATE NOT NULL
    , t_value TIME(0) NOT NULL
);


DECLARE @d DATETIME = '2016-09-20 09:34:00';

INSERT INTO #DT (dt_value, d_value, t_value)
SELECT @d, CONVERT(DATE, @d), CONVERT(TIME(0), @d);

SET @d = '2016-09-20 11:34:00';

INSERT INTO #DT (dt_value, d_value, t_value)
SELECT @d, CONVERT(DATE, @d), CONVERT(TIME(0), @d);

/* show all rows with a date after 2016-07-01 11:00 am */
SELECT *
FROM #DT dt
WHERE dt.dt_value >= '2016-07-01 11:00:00';

/* show all rows with a date after 2016-07-01 11:00 am */
SELECT *
FROM #DT dt
WHERE dt.d_value >= CONVERT(DATE, '2016-07-01')
    AND dt.t_value >= CONVERT(TIME(0), '11:00:00');

W powyższym kodzie tworzymy tabelę testową, zapełniając ją dwiema wartościami, a następnie wykonując proste zapytanie względem tych danych. Pierwszy SELECTzwraca oba wiersze, jednak drugi SELECTzwraca tylko jeden wiersz, co może nie być pożądanym rezultatem:

Prawidłowy sposób filtrowania zakresu dat / godzin, w którym wartości znajdują się w osobnych kolumnach, jak wskazał @ypercube w komentarzach, to:

WHERE dt.d_value > CONVERT(DATE, '2016-07-01') /* note there is no time component here */
    OR (
        dt.d_value = CONVERT(DATE, '2016-07-01') 
        AND dt.t_value >= CONVERT(TIME(0), '11:00:00')
    )

Jeśli potrzebujesz komponentu czasu oddzielonego do celów analizy , możesz rozważyć dodanie obliczonej, utrwalonej kolumny dla części czasowej wartości:

ALTER TABLE #DT
ADD dt_value_time AS CONVERT(TIME(0), dt_value) PERSISTED;

SELECT *
FROM #dt;

Utrwalona kolumna może być następnie zaindeksowana, umożliwiając szybkie sortowanie itp. Według pory dnia.

Jeśli zastanawiasz się nad podzieleniem daty i godziny na dwa pola do celów wyświetlania, powinieneś zdawać sobie sprawę, że formatowanie powinno być wykonywane na kliencie, a nie na serwerze.

Zamierzam przedstawić odmienne zdanie na temat innych odpowiedzi.

Jeśli oba elementy daty i godziny są wymagane razem, tj. Wpis jest niepoprawny, jeśli zawiera jeden, ale nie drugi (lub ma wartość NULL w jednej, ale nie drugiej), wówczas przechowywanie go w jednej kolumnie ma sens z powodów podanych w innych odpowiedzi

Może się jednak zdarzyć, że jeden lub oba komponenty są indywidualnie opcjonalne. W takim przypadku niewłaściwe byłoby przechowywanie go w jednej kolumnie. Takie postępowanie zmusiłoby Cię do przedstawienia wartości NULL w dowolny sposób, np. Zapisanie czasu jako 00:00:00.

Oto kilka przykładów:

• Rejestrujesz przejazdy pojazdów w celu odliczenia podatku za przebieg. Przydałaby się znajomość dokładnej godziny podróży, ale jeśli pracownik nie zanotował jej i zapomniał, data powinna być nadal zapisywana sama (wymagana data, opcjonalny czas).

• Przeprowadzasz ankietę, aby dowiedzieć się, o której godzinie ludzie jedzą lunch, i prosisz uczestników o wypełnienie formularza próbką swoich porcji obiadowych, w tym dat. Niektórzy nie zawracają sobie głowy wpisaniem daty i nie chcesz odrzucić danych, ponieważ są to chwile, na których naprawdę Ci zależy (opcjonalna data, wymagany czas).

Zobacz to powiązane pytanie dotyczące alternatywnych podejść.

Zawsze wolę przechowywać to jako pojedynczą kolumnę, chyba że istnieje określone zapotrzebowanie biznesowe / aplikacji. Poniżej moje punkty -

• Wyodrębnianie czasu ze znacznika czasu nie stanowi problemu
• Po co dodawać dodatkową kolumnę tylko na czas, jeśli możemy przechowywać obie razem
• Aby uniknąć dodawania daty i godziny za każdym razem, gdy pytasz.

W SQL Server najlepiej przechowywać DataTime jako jedno pole. Jeśli utworzysz indeks w kolumnie DataTime, można go użyć jako wyszukiwania daty i wyszukiwania daty. Dlatego jeśli chcesz ograniczyć wszystkie rekordy istniejące dla określonej daty, możesz nadal korzystać z indeksu bez konieczności robienia czegokolwiek specjalnego. Jeśli chcesz zapytać o porcję czasu, nie będziesz w stanie użyć tego samego indeksu, a zatem jeśli masz przypadek biznesowy, w którym zależy Ci bardziej na czasie niż na DateTime, powinieneś przechowywać go osobno, ponieważ będziesz musiał utworzyć indeks na nim i poprawić wydajność.

Rzeczywiście szkoda, że ​​nie ma w tym celu standardowego typu cross-DBMS (np. INT i VARCHAR są liczbami całkowitymi i ciągami znaków). 2 podejścia, które do tej pory spotkałem w wielu bazach danych, wykorzystują kolumny VARCHAR / CHAR do przechowywania wartości DataTime jako ciągów sformatowanych zgodnie z normą ISO 8601 (wygodniej, czytelny dla człowieka) i użycie BIGINT do przechowywania ich jako znaczników czasu POSIX (przechowywanych więcej wydajnie, szybciej, łatwiej manipulować matematycznie).

Po przeczytaniu wielu rzeczy, czas UTC Unix w BIGINT wydaje się optymalnym rozwiązaniem. Identyfikator czasu TZDB w VARCHAR do przechowywania strefy czasowej w razie potrzeby. Kilka argumentów:

1. TIMESTAMP i DATETIME wykonują kilka chwytliwych konwersji w tle, które wydają się złożone i niejasne. Serwer czasami przełącza się z czasu lokalnego na UTC lub na czas serwera iz powrotem. Kilka ukrytych kosztów ogólnych dla każdej funkcji.

2. BIGINT (8kb) jest co najmniej tak lekki lub lżejszy niż DECIMAL wymagany do przechowywania w formacie xxxxxx.xxxxxx, który jest praktycznie przechowywany przez MySQL jako dwie INT + coś . I wystarczy przechowywać stulecia przed nami.

3. Prawie wszystkie główne języki programowania mają biblioteki standardowych funkcji do pracy z czasem uniksowym.

4. Operacje matematyczne z BIGINT powinny być tak szybkie lub szybsze niż cokolwiek innego na jakimkolwiek sprzęcie.

Oczywiście wszystkie powyższe dotyczą dużych, międzynarodowych projektów. W przypadku czegoś małego wybór domyślnego formatu wybranego frameworka wydaje się wystarczający.