Care este un caz de utilizare valid pentru utilizarea TIMESTAMP FĂRĂ ZONA ORARĂ? (Administrarea bazelor de date, Postgresql, Timestamp)

Marcus Junius Brutus a intrebat.

Există un răspuns lung și destul de elucidator cu privire la diferențele dintre

  • TIMESTAMP WITH TIME ZONE-vs-
  • TIMESTAMP WITHOUT TIME ZONE

disponibil în postul SO Ignorarea totală a fusurilor orare în Rails și PostgreSQL.

Ceea ce aș dori să știu este: Există cazuri de utilizare valabile pentru a utiliza de fapt TIMESTAMP WITHOUT TIME ZONE sau ar trebui să fie considerat un anti-pattern?

Comentarii

  • Această întrebare riscă să fie închisă fiind „bazată în primul rând pe opinii”. Am încercat să dau câteva puncte de vedere obiective în răspunsul meu de mai jos. –  > Por Colin ‘t Hart.
  • Această problemă este destul de reală, fiecare tip de date având o semnificație foarte diferită. Așadar, cu siguranță nu aș considera această întrebare ca fiind bazată în principal pe opinii. –  > Por Basil Bourque.
5 răspunsuri
MatheusOl

Acest lucru este afirmat în multe locuri, dar cred că merită menționat întotdeauna când comparăm timestamp with time zone cu timestamp without time zone tipuri: timestamp WITH time zone nu stochează fusul orar împreună cu data și ora. Ceea ce face este să stocheze toate datele în fusul orar UTC, așa cum se menționează în documentație:

Pentru timestamp cu fus orar, valoarea stocată intern este întotdeauna în UTC (Universal Coordinated Time, cunoscut în mod tradițional ca Greenwich Mean Time, GMT). O valoare de intrare care are specificat un fus orar explicit este convertită în UTC utilizând decalajul corespunzător pentru fusul orar respectiv. Dacă în șirul de date de intrare nu este specificat niciun fus orar, se presupune că se află în fusul orar indicat de parametrul TimeZone al sistemului și este convertit în UTC utilizând decalajul pentru fusul orar.

Se consideră că este valabil ca unii să utilizeze timestamp WITHOUT time zone în situațiile în care (1) totul se află în același fus orar sau (2) stratul de aplicație se ocupă de fusul orar și doar stochează totul într-un anumit fus orar (de obicei UTC). Dar este, de asemenea, considerat un anti-pattern, pur și simplu pentru că soluția corectă pentru (1) este de a configura setarea TimeZone la un anumit fus orar pentru sistem, iar (2) este deja rezolvată, deoarece PostgreSQL stochează deja totul în același fus orar (UTC).

Acum, după ce am rezolvat aceste două probleme, pot găsi un singur motiv bun pentru a utiliza timestamp WITHOUT time zone. Acesta este atunci când vrei să stochezi evenimente în viitor și când trebuie declanșat un fel de alertă atunci când ajungem la acel moment. Acest lucru ar putea fi bun pentru timestamp WITH time zone dacă, și numai dacă, regulile definite de legile regiunii cu privire la fusul orar nu s-ar schimba niciodată. Cea mai frecventă regulă care se schimbă este cea referitoare la adoptarea sau nu a orei de vară (DST).

De exemplu, imaginați-vă că vă aflați, să zicem, la, să zicem, 2013-06-15 (care nu este încă în DST) programați un eveniment care să aibă loc la 2013-01-15 10:00 (care ar fi deja în DST), iar la 2013-06-15 regiunea dvs. a fost desemnată să adopte DST; dar, la ceva timp după aceea, guvernul a schimbat regula și spune că regiunea dvs. nu va mai folosi DST, și brusc ora programată devine 2013-01-15 11:00 (în loc de 10:00), că dacă folosiți timestamp WITH time zone, și păstrați configurațiile TZ actualizate. Desigur, puteți observa că este posibil să tratați astfel de cazuri și cu fusul orar, dacă urmăriți modificările regulilor în regiunile/zonele orare care vă interesează și actualizați înregistrările afectate.

Merită menționat faptul că unele regiuni schimbă adesea aceste reguli (ca în Brazilia, unele state – nu întreaga țară – se schimbă adesea), dar în majoritatea cazurilor se schimbă foarte devreme, astfel încât utilizatorii dvs. vor fi afectați doar de evenimente programate la o distanță foarte mare de ora curentă.

Acestea fiind spuse, mai am doar o singură sugestie. Dacă aveți utilizatori, jurnale sau orice altceva în fus orar diferit, stocați undeva fusul orar din care provin și alegeți și folosiți timestamp with time zone. În acest fel, puteți (1) să încrucișați evenimentele care se întâmplă mai aproape unul de celălalt pentru diferite surse (independent de fusurile orare ale acestora) și (2) să arătați ora inițială (ora ceasului) la care s-a întâmplat evenimentul.

Comentarii

  • codeblog.jonskeet.uk/2019/03/27/… discută în profunzime exact acest scenariu „future-event-rules-might-change” (cu aceeași concluzie) –  > Por Beni Cherniavsky-Paskin.
Basil Bourque

Da. Există cazuri de utilizare pentru TIMESTAMP WITHOUT TIME ZONE.

  • În aplicațiile de afaceri obișnuite, acest tip ar fi utilizat doar pentru:
    • Rezervarea de întâlniri viitoare
    • Reprezentarea aceleiași ore a zilei în diferite fusuri orare, cum ar fi ora 12:00 a zilei de 23 în Tokyo. și în Paris (două momente diferite la ore distanță, aceeași oră a zilei).
  • Pentru urmărirea momentelor, puncte specifice de pe linia de timp, utilizați întotdeauna TIMESTAMP WITH TIME ZONE, nu WITHOUT.

TIMESTAMP WITHOUT TIME ZONE Valorile sunt nu un punct de pe linia de timp, nu momente reale. Ele reprezintă o idee aproximativă despre potențialul momente, puncte posibile pe linia de timp de-a lungul unui interval de aproximativ 26-27 de ore (intervalul de fus orar de pe glob). Ele nu au nicio semnificație reală până când nu aplicați un fus orar sau offset-from-UTC.

Ex: Crăciunul

De exemplu, să spunem că trebuie să înregistrați începutul sărbătorilor/zilelor sfinte.

Table: holiday_

Column: year_         Type: SMALLINT
Column: description_  Type: VARCHAR
Column: start_        Type: TIMESTAMP WITHOUT TIME ZONE

Pentru a înregistra faptul că anul acesta Crăciunul începe după miezul nopții pe 25 decembrie, trebuie să spunem 2016-12-25 00:00:00 fără niciun fus orar. La începutul zilei, Moș Crăciun vizitează Auckland, în Noua Zeelandă, imediat după miezul nopții, deoarece aceasta este una dintre cele mai timpurii miezuri de noapte de pe glob. Apoi își croiește drum spre vest, la următorul miez de noapte, ajungând în curând în Filipine. Apoi, renii se deplasează în direcția vest, ajungând în India la miezul nopții, care se întâmplă să aibă loc la câteva ore după miezul nopții din Auckland. Mult mai târziu este miezul nopții la Paris, în Franța, și tot mai târziu este miezul nopții la Montreal, în California. Toate aceste vizite ale lui Moș Crăciun au loc la momente diferite în timpdar toate s-au întâmplat la scurt timp după miezul nopții, conform miezului nopții din fiecare localitate.

Deci, înregistrarea 2016-12-25 00:00:00 fără nici un fus orar ca fiind începutul Crăciunului este informativă și legitimă, dar numai vag. Până când nu se spune „Crăciun în Auckland” sau „Crăciun în Montreal”, nu avem un moment specific în timp. Dacă înregistrați momentul real de fiecare dată când sania a atins pământul, ați folosi TIMESTAMP WITH TIME ZONE în loc de WITHOUT tipul.

Similar cu Crăciunul este ajunul Anului Nou. Atunci când Times Square Ball cade în New York, oamenii din Seattle încă își răcesc șampania și își pregătesc claxoane de petrecere. Cu toate acestea, noi am înregistra ideea a momentului de Anul Nou ca fiind 2017-01-01 00:00:00 într-o TIMESTAMP WITHOUT TIME ZONE. În schimb, dacă dorim să înregistrăm momentul în care mingea a căzut în New York sau când oamenii din Seattle au suflat în goarnă, vom folosi în schimb TIMESTAMP WITH TIME ZONE (nu WITHOUT) pentru a înregistra aceste momente reale, fiecare la trei ore distanță unul de celălalt.

De exemplu: Schimburile din fabrică

Un alt exemplu ar putea fi înregistrarea unei politici care implică ora de la ceasul de perete în diferite locații. Să spunem că avem fabrici în Detroit, Düsseldorf și Delhi. Dacă spunem că în toate cele trei fabrici primul schimb începe la ora 6 dimineața, cu o pauză de masă la 11:30, acest lucru ar putea fi înregistrat ca o TIMESTAMP WITHOUT TIME ZONE. Din nou, această informație este utilă într-un mod vag, dar nu indică un moment specific în timp până când nu aplicăm un fus orar. O nouă zi răsare mai devreme în est. Astfel, fabrica din Delhi va fi prima care se va deschide la ora 6 AM. Câteva ore mai târziu, fabrica din Düsseldorf își începe activitatea la ora 6 dimineața. Dar fabrica din Detroit nu se va deschide de fapt decât șase ore mai târziu, la ora 6 dimineața.

Puneți în contrast această idee (a momentului în care începe, în general, tura de lucru în fabrică) cu faptul istoric al momentului în care fiecare muncitor din fabrică a fost înregistrat pentru a-și începe tura într-o anumită zi. Ceasul de intrare este un moment real, un punct real pe linia timpului. Prin urmare, vom înregistra acest lucru într-o coloană de tipul TIMESTAMP WITH TIME ZONE în loc de WITHOUT tip.

Așadar, da, există cazuri de utilizare legitime pentru TIMESTAMP WITHOUT TIME ZONE. Dar, din experiența mea cu aplicațiile de afaceri, acestea sunt relativ rare. În afaceri, avem tendința de a ne interesa momentele reale: când a sosit factura, când anume intră în vigoare contractul, în ce moment a fost executată tranzacția bancară. Așadar, în astfel de situații obișnuite, ne dorim ca TIMESTAMP WITH TIME ZONE tip.

Pentru mai multe discuții, consultați răspunsul meu la întrebarea similară, Ar trebui să stochez timestamps UTC sau ora locală pentru schimburi

Postgres

Rețineți că Postgres în mod specific niciodată salvează niciodată informațiile despre fusul orar specificat la inserarea unui timestamp.

  • TIMESTAMP WITH TIME ZONE
    • Orice fus orar specificat sau decalaj inclus cu datele de intrare este utilizat pentru a ajusta valoarea la UTC și este stocat. Informațiile privind zona/ decalajul transmise sunt apoi eliminate. Gândiți-vă la TIMESTAMP WITH TIME ZONE ca la TIMESTAMP WITH RESPECT FOR TIME ZONE.
    • O intrare de 12:00 la 7 martie anul curent în India va fi ajustată la ora UTC prin scăderea a cinci ore și jumătate: 6:30 AM.
  • TIMESTAMP WITHOUT TIME ZONE
    • Orice fus orar sau decalaj specificat inclus în datele de intrare este ignorat în totalitate.
    • O intrare de la ora 12:00 la 7 martie anul curent în India este înregistrată ca fiind ora 12:00 la 7 martie anul curent, fără nicio ajustare.

Standardul SQL abia dacă abordează aspectele legate de tipurile de date și de comportamentul datelor de dată-timp. Astfel, baza de date variază foarte mult între ele în ceea ce privește manipularea datei-timp.

Comentarii

  • În plus, în cazul turelor din fabrică (sau din spital), oricine lucrează în tura de cimitir în zilele de trecere la DST va avea orele înregistrate în mod incorect de este înregistrat fără fus orar… –  > Por Jasen.
  • Cred că există o greșeală de scriere în ultimul exemplu: „O intrare de la ora 12:00 pe 7 martie .. ar trebui să fie „înregistrată ca”. 12:00′ (nu 21:00) –  > Por TmTron.
  • @Jasen Atunci când înregistrarea timpului a funcționat efectiv, urmăriți un moment, un anumit punct de pe linia de timp. Pentru a urmări un moment, faceți următoarele nu utilizați TIMESTAMP WITHOUT TIME ZONE. Pentru a urmări un moment, cum ar fi momentul în care o persoană a început și a terminat tura de lucru, utilizați TIMESTAMP WITH TIME ZONE. În codul Java, aceasta ar fi ZonedDateTime shiftEnded = ZonedDateTime.of( 2020 , 1 , 23 , 2 , 0 , 0 , 0 , ZoneId.of( "Africa/Tunis" ) ) ;. Apoi se scrie în coloana de tip TIMESTAMP WITH TIME ZONE: myPreparedStatement.setObject( shiftEnded.toOffsetDateTime() ) ; –  > Por Basil Bourque.
Shay Rojansky

Aș dori să adaug un alt punct de vedere la acest lucru, care contrazice multe din ceea ce s-a scris în celelalte răspunsuri. În opinia mea timestamp with time zone are foarte puține cazuri de utilizare valabile, iar timestamp without time zone ar trebui, în general, să fie preferat.

În primul rând, trebuie să înțelegeți modelul „UTC peste tot”, care este recomandat în general pentru toate aplicațiile care nu au cerințe foarte speciale. Aceasta înseamnă pur și simplu că aplicația dvs. ar trebui să reprezinte toate timestamp-urile sale în UTC, peste tot, tot timpul. Bineînțeles, veți afișa data/ora locală utilizatorilor, dar ideea este de a le converti chiar la marginea programului, la redarea vizualizării. Acesta este locul potrivit pentru a combina marca de timp UTC (pe care este posibil să o fi încărcat dintr-o bază de date) și fusul orar al utilizatorului (care poate proveni din browser) într-o marcă de timp locală.

Dacă urmați acest model, atunci timestamp with time zone este un antimodel. Tot ceea ce face acest tip este să facă PostgreSQL să efectueze conversii de fus orar atunci când citește și scrie timestamp-uri, pe baza datelor dumneavoastră PostgreSQL TimeZone variabilă de sesiune. În loc să vă ocupați de fusurile orare chiar la marginea aplicației, le-ați introdus în inima acesteia, chiar în comunicarea cu baza de date. Trebuie să aveți grijă ca întotdeauna PostgreSQL TimeZone configurat corespunzător în orice moment, adăugând un alt punct de eșec și confuzie inutilă.

Și pentru ce? Dacă urmați modelul UTC pretutindeni, aplicația dvs. are oricum doar timestamp-uri UTC; deci de ce să cereți bazei de date să facă conversii de fus orar pentru acestea? Le puteți stoca pur și simplu într-un fișier timestamp without time zone coloană și să o tratați ca și cum ar fi întotdeauna UTC. Fără conversii, fără zone orare, fără complicații.

Comentarii

  • Cred că susținătorii timestamptz susțin, de asemenea, modelul „UTC peste tot”. Doar că regula este aplicată la nivelul bazei de date în loc să se bazeze doar pe dezvoltatorii de aplicații/API pentru a o aplica? Dacă ești capabil să impui această practică, de ce nu? De asemenea, puteți impune ca toate conexiunile postgres să seteze fusul orar la UTC. Chiar dacă nu o fac, formatul ISO va conține decalajul tz. Deci, nu este același lucru ca și UTC peste tot, dar aplicat? –  > Por iamnat.
  • În primul rând, dacă TimeZone-ul PostgreSQL este setat la orice altceva în afară de UTC și dacă utilizați timestamptz, programul dvs. citește timestamps non-UTC. Pur și simplu, acesta nu este modelul „UTC peste tot” și, în funcție de limbajul clientului, poate crea sau nu o mulțime de complicații. Ori sunteți UTC peste tot, ori folosiți timestamps locale…. –  > Por Shay Rojansky.
  • Încă o dată, este foarte ușor să ne imaginăm o nouă instanță PostgreSQL fiind configurată pe un server oarecare, păstrând din greșeală fusul orar local al mașinii, care este configurat în mod implicit. Aplicațiile citesc de pe acest server și obțin timestamp-uri locale într-un fus orar total arbitrar (în care se întâmplă ca serverul să se afle). De ce am dori această complicație suplimentară? –  > Por Shay Rojansky.
  • S-ar putea, dar atunci ar avea și mai puțin sens să folosim timestamptz. Scopul acestui tip este de a efectua conversii de fus orar ca valori citite și scrise în PostgreSQL (în baza de date, în mod intern, marcajul temporal este întotdeauna salvat ca UTC). Setarea permanentă a fusului orar al sesiunii la UTC dezactivează efectiv aceste conversii, deci de ce să se utilizeze timestamptz deloc? Altfel spus, dacă valorile din baza de date sunt UTC, iar valorile care intră și ies din baza de date sunt întotdeauna UTC, atunci de ce trebuie să existe o conștientizare a fusului orar în baza de date? –  > Por Shay Rojansky.
  • folosiți timestamp TZ pentru că are informații precise și clare, mai degrabă decât să vă bazați pe o convenție precum „UTC peste tot” pentru ca valoarea sa să fie interpretată. Setările de fus orar ale clienților pot fi predefinite la UTC prin alter database NAME set time zone 'UTC'; –  > Por Jasen.
Colin ‘t Hart

Site-ul date timestamp, de asemenea, nu include informații despre fusul orar, însă o mulțime de oameni îl folosesc.

Bineînțeles, acest lucru în sine nu reprezintă un răspuns.

Este valabil să se utilizeze timestamp și date pentru orice marcaje de timp și date care sunt întotdeauna în același fus orar sau care sunt stocate în raport cu un fus orar cunoscut.

Dacă fusul orar este întotdeauna același sau implicit, atunci este mai degrabă un antipattern să fie stocat decât să nu fie stocat (informație redundantă).

Timestamp-urile pot fi, de asemenea, utilizate pentru a stoca informații tehnice, cum ar fi cele utilizate în jurnalele aplicațiilor sau pentru măsurarea performanțelor. În acest caz, fusul orar poate fi, de asemenea, lipsit de importanță.


În schimb, dacă proiectați sau lucrați la un sistem distribuit sau în orice sistem în care părți ale sistemului vor fi distribuite în diferite fusuri orare, ar putea fi considerat un antimodel să nu folosiți timestamp with timezone, deși unele sisteme pot fi concepute pentru a funcționa într-un singur fus orar, de exemplu UTC. În acest caz, logica fusului orar poate fi introdusă în stratul de aplicație – dar aș considera că este un anti-pattern, da.

Comentarii

  • Înțeleg că, în anumite cazuri, nu strică să folosiți timestamp without timezone, dar îmi aduce vreodată ceva? Altfel, de ce m-aș deranja dacă timestamp with timezone data tipul este întotdeauna la fel sau mai adecvat? –  > Por Marcus Junius Brutus.
  • A nu stoca informații redundante ar fi principalul meu argument. Presupun că aritmetica datelor pe timestamp without timezone ar fi și ea mai rapidă, dar probabil că acest lucru este important doar dacă procesați miliarde de rânduri… –  > Por Colin ‘t Hart.
  • @Colin’tHart timestamp și timestamptz sunt ambele stocate în același mod. Nu există informații despre fusul orar stocate într-un timestamptz, ci sunt convertite în UTC pentru stocare. Aș spune să folosiți întotdeauna timestamptz atunci când marcajele de timp în cauză denotă timp absolut. Asta este tot ceea ce timestamptz înseamnă. Am scris despre acest lucru aici. –  > Por fphilipe.
Steve Fosdick

Poate părea tentant să stocați și să procesați datele/orele în ora locală dacă aplicația dumneavoastră este limitată la un singur fus orar, dar cred că este o greșeală.

La revenirea de la ora de vară la ora standard se repetă o oră în ora locală (presupunând că diferența este de o oră). Acolo unde locuiesc eu, acest lucru se întâmplă de obicei în jurul orei 2 dimineața, astfel încât ora locală este 01:58, 01:59, 01:00 și avansează la 02:00 doar la sfârșitul orei repetate.

Dacă încercați să ordonați evenimentele în funcție de timp folosind ora locală, înseamnă că evenimentele din două ore diferite sunt amestecate și nu apar în ordinea în care s-au întâmplat de fapt.

Prin comparație, UTC nu are această problemă și ordonează în mod clar. Prin urmare, chiar și atunci când lucrați cu un singur fus orar, doriți ca baza de date să stocheze și să proceseze orele în UTC și să le convertească în/din ora locală pentru introducere/afișare. Acesta este comportamentul TIMESTAMP WITH TIME ZONE.