Przechowywanie daty w bazie danych

Potrzebuję porady odnośnie przechowywania dat. Nigdy się na tym nie zastanawiałem, aż do czasu odkrycia, że różne języki/biblioteki różnie podchodzą do tematu. Format dat, który chciałbym zwracać to rok-miesiąc-dzieńTgodzina:minuty:sekundy.milisekundy, czyli w bazie stringi YYYY-MM-DDTHH:mm:ss.SSS, ale... UTC, a czas lokalny... Nie wiem...

Z tego co zauważyłem są jakieś ISO, ISO 8601 stringi z sufiksami Z, +00 i inne cuda na kiju. Zapisywać daty w czasie UTC? W jakim formacie? Jak potem taki czas zwracać przez REST?

Jak właściwie zawsze, sprawa się rozbija o to, czego tak naprawdę potrzebujesz — jak często musisz zwracać datę (w odróżnieniu od wykonywania obliczeń typu „jaka jest różnica pomiędzy tymi dwoma datami”), ile masz różnych stref czasowych i jak często musisz coś przeliczać między nimi…

Jak masz jakąś bazę SQL-ową, to one mają swój typ pola do przechowywania daty i nie ma z tym specjalnie co walczyć. Jak jesteś pewien, że Cię interesuje prawie zawsze jedna strefa czasowa i ta strefa się nie zmieni (np. to są dane zamówień w sklepie internetowym — bardzo mała szansa, że przeniesiesz backend do innej strefy, żeby Cię to nagle zaczęło obchodzić), to możesz trzymać czas lokalny, ale tak czy owak sugerowałbym UTC, żeby nie mieć problemów z czasem letnim.

A jak klepiesz coś swojego, to zazwyczaj zamiast trzymania stringów bardziej użyteczne będzie trzymanie liczby sekund od 1970-01-01 i operowanie na tym.

Dat się NIE ZAPISUJE w bazie jako string, ale jako typ datowy
Podobnie jako to jakiś język wyjmuje z bazy i przetwarza, też w typie datowym

Formatuje się do stringa tylko na zakończeniach, czy jak się SERIALIZUJE do API, czy output dla człowieka

AnyKtokolwiek napisał(a):

Dat się NIE ZAPISUJE w bazie jako string, ale jako typ datowy

Podobnie jako to jakiś język wyjmuje z bazy i przetwarza, też w typie datowym

@revenger nie powiedziałeś czego używasz, ale rozwijając wypowiedź @AnyKtokolwiek

• Przykład bazy danych - Typy daty i czasu używane w Posgresie Date/Time Types
• Przykład języka - Typy do daty i czasu używane w Javie Package java.time (Wyjątkowo w Javie zrobili to dobrze, bo wzorowali się na bibliotece Joda Time)

Jedyny sensowny sposób trzymania daty w relacyjnej bazie danych to Timestamp/Date / Datetime / Time / Interval. Moim zdaniem najsensowniej jest trzymać wszystko w UTC. W każdym innym przypadku wpakujesz się w piekło. Prezentować użytkownikowi możesz jak chcesz, zmienić strefę czasową też zawsze można na taką, która jest dogodna dla użytkownika. Nie jestem w stanie wymyślić powodu do trzymania daty łącznie ze strefą czasową.
Trzymanie dat w char / varchar świadczy o upośledzeniu w stopniu znacznym. Proste znalezienie rekordów z zeszłego roku robi się mission impossible, bo najpierw będziesz się użerać z napisaniem zapytania, później z jego wydajnością, a na koniec się okaże, że ktoś gdzieś jednak wpisał w innym formacie i zapytanie się wywala na twarz.
UTC ma wielką zaletę - brak czasu letniego / zimowego, korekt, zastanawiania się, czy godzina jest już po, czy przed zmianą czasu.

Moim zdaniem najsensowniej jest trzymać wszystko w UTC.

Otóż nie zawsze. Przykładowo jeśli przechowujesz np. godziny spotkań w jakimś miejscu (ze spotkaniami on-line jest trudniej) to przechowujesz je w postaci daty w czasie lokalnym bez strefy. Bo jeśli chcesz się spotkać o 16:00, to chcesz się spotkać o 16:00 a nie o 15:00 bo w międzyczasie zmieniła się strefa czasowa.

@hauleth: Ze spotkaniami online jest ciekawie bo musisz dorzucić strefę czasową albo jej odpowiednik, a ta może być zapisana na dwa sposoby:

• zły sposób, z offsetem (np UTC+02:00), zły bo jak zmienią się np reguły DST to nie przeliczysz tego chyba że manualnie zaaplikujesz zmiany (czego nikt nie robi)
• lepszy sposób, z lokalizacją głównej osoby, np Europe/Warsaw - minus tego rozwiązania jest taki że w przypadku rozpadu krajów i wojen domowych w teorii jesteś to w stanie obsłużyć, o ile przewidziałeś że np Rzeszów ogłosi niepodległość; minus jest taki że lista i forma takich stref czasowych jest niekompatybilna między różnymi środowiskami

Stąd generalnie ma się trzy rodzaje czasów:

• lokalny
• z offsetem
• z zoną

i trzeba zwrócić uwagę jak to jest przeliczane na interfejsach, np z różnymi bazami danych

@hauleth: Jak osoba A i osoba B mają się spotkać, to muszą trafić "gdzieś" w tym samym momencie (instant). Nie ważne czy posługują się strefą UTC, CET, PST, czy tą przesuniętą o jeden dzień na Pacyfiku, bo chcieli mieć wcześniej Sylwestra. W tym momencie, w Greenwich będzie jakaś godzina (powiedzmy "uniwersalna"). Nauczyłem się myśleć o strefie czasowe jak o formacie daty - jedni wolą format od dnia, inni od roku, a jeszcze inni amerykański.

Dość ostro przećwiczyłem te opcje robiąc system do umawiania spotkań (albo to daty mnie ostro przećwiczyły), ciężko będzie mnie przekonać :) W sumie czas lokalny ma znaczenie w jednym przypadku - kiedy zależy ci na wiedzy jak daną sytuację postrzega jakiś użytkownik / obiekt. Np. nie chcesz umawiać spotkań gdzie którykolwiek użytkownik będzie musiał się wdzwonić o 4:00

Sory, mogłem jeszcze dopisać, że jak to wygląda w kontekście bazki sqlite dla której nie ma DATE lub DATETIME... Z racji, że bawię się SequelizeORM, to domyślnie zapisuje mi do bazki czas UTC w formacie jak poniżej. Nie wiem co myśleć :P Dobre? Tragiczne?

Dokumentacja mówi żeby uzywać TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). bo spina się wtedy automatycznie z funkcjami do obsługi dat w sqlite i tego bym się trzymał.

Epoch time