Jak najlepiej przechowywać znacznik czasu w PostgreSQL?

20

Pracuję nad projektem PostgreSQL DB i zastanawiam się, jak najlepiej przechowywać znaczniki czasu.

Założenia

Użytkownicy w różnych strefach czasowych będą korzystać z bazy danych dla wszystkich funkcji CRUD.

Przejrzałem 2 opcje:

timestamp NOT NULL DEFAULT (now() AT TIME ZONE 'UTC')
bigint NOT NULL DEFAULT

Dla timestampchciałbym wysłać ciąg, który reprezentuje dokładną (UTC) znacznik czasu w tej chwili INSERT.

Dla bigintchciałbym przechowywać dokładnie to samo, ale w formacie liczbowym. (problemy ze strefą czasową są obsługiwane przed przekazaniem millis na serwer, więc zawsze millis w UTC).

Jedną z głównych zalet przechowywania a bigintmoże być to, że łatwiej byłoby przechowywać i odzyskiwać, ponieważ przekazywanie poprawnie sformatowanego znacznika czasu jest bardziej złożone niż zwykła liczba (milis od czasu wydania Uniksa).

Moje pytanie brzmi, które pozwoliłoby na najbardziej elastyczny projekt i jakie byłyby pułapki każdego podejścia.

— Bam
źródło

Istnieje wiele powodów, dla których znacznik czasu jest lepszy niż bigint do reprezentowania znaczników czasu. Nie mogę wymyślić żadnego powodu, dla którego bigint byłby lepszy niż znacznik czasu.

— Lennart

Głównym powodem, dla którego uważam, że BigInt może być łatwiejszy, jest to, że łatwiej jest go odzyskać i przechowywać. Zaktualizuję moje pytania.

— Bam

23

Przechowuj znaczniki czasu jako timestamp, a raczej timestamptz( timestamp with time zone), ponieważ masz do czynienia z wieloma strefami czasowymi . Wymusza to prawidłowe dane i zazwyczaj jest najbardziej wydajne. Pamiętaj, aby zrozumieć typ danych, istnieją pewne nieporozumienia wokół:

Aby rozwiązać problem:

przekazanie poprawnie sformatowanego znacznika czasu jest bardziej złożone niż zwykła liczba

Możesz przekazać i pobrać epokę UNIX w dowolny sposób, jeśli wolisz:

SELECT to_timestamp(1437346800)
     , extract(epoch FROM timestamptz '2015-07-20 01:00+02');

Związane z:

Jeśli chcesz zapisać bieżący znacznik czasu z zapisem do bazy danych, użyj timestamptz kolumny z wartością domyślnąnow() . Czas systemowy na serwerze DB jest zwykle o wiele bardziej niezawodny i spójny niż wielu klientów podających swoje wyobrażenie o czasie.
Na INSERTto może być tak proste, jak:

CREATE TABLE foo (
  ... -- other columns
, created_at timestamptz NOT NULL DEFAULT now()
);

I po prostu nie pisz do tej kolumny. Jest wypełniany automatycznie.

— Erwin Brandstetter
źródło

To wyjaśniło. Oprócz tego, że znaczniki czasu są przechowywane jako 8-bajtowe liczby całkowite, niezbędne tak samo jak przechowywanie jako bigint, przechowywanie i wyszukiwanie za pomocą funkcji „to_timestamp” sprawia, że jest to znacznie prostszy wybór. Dziękuję

— Bam

8

Zawsze należy przechowywać dane w rodzimym typie danych, aby móc korzystać z wbudowanych funkcji. A typ danych znacznika czasu jest oczywiście a timestamp.

Btw, a nietimestamp jest przechowywany jako ciąg, jest przechowywany jako 8-bajtowa liczba całkowita, dokładnie taka sama jak : dokumentacja PostgreSQL .bigint

— dnoeth
źródło

Przepraszam, chciałem powiedzieć, że wyślę ciąg do przechowywania, a nie przechowuję znacznik czasu. Poprawiłem to

— Bam