Vlákno názorů k článku Datové typy v PostgreSQL pro práci s datem a časem od jviki - Ze zkušenosti musím naprosto nesouhlasit s upřednostňováním timestamp...

Ze zkušenosti musím naprosto nesouhlasit s upřednostňováním timestamp with time zone před timestamp without time zone.

Pokud máte např. 3 kooperující systémy, v mém případě nějaký sběrný bod, ze kterého chodí data, dále serverovou aplikaci, která komunikuje s databází, a samotný databázový systém, tak musíte zajistit, že všechny systémy mají shodné nastavení časových zón, anebo musíte zajistit explicitní přepočet časové zóny na rozhraních.

Nelze očekávat, že všichni klienti komunikující s databází mají nastavenou stejnou zónu (hrozí tu mimochodem race condition právě např. na rozhraní letní / zimní čas).

Timestamp with time zone dále komplikuje ladění, protože mi databáze dává interpretovaná data a nikoliv uložená data. Daleko hůře se odhalují chyby spojené právě s datem a časem.

Osobně zastávám názor, že časové značky by měly být na aplikační úrovni (a níže, tedy v DB) vždy v UTC. Konkrétní časové zóny jsou totiž závislé na kontextu - tedy koncovém uživateli.

Používáním timestamp with time zone jsem se už dost spálil. Takže implicitní chování timestamp mi připadá v pořádku. Výhrady, které uvádíte mi často připadají přesně opačné. Např. je dobře, že timestamp není ovlivněn přechody mezi letním a zimním časem.

Připadá mi, že jste to napsal přesně opačně. Když ukládáte čas i s časovou zónou, nemusíte řešit, kdo jakou časovou zónu má, protože to vždy dokážete převést na libovolnou jinou časovou zónu. Když máte čas bez časové zóny, musíte naopak mít údaj o časové zóně uložený někde externě (např. implicitně, že se vždy používá „naše“ časová zóna, lokální, v případě ČR tedy Europe/Prague). Pak právě nastávají třeba ty problémy se změnou času, protože když vám přijde čas 27. 10. 2019 02:30:00, tak nevíte, jestli je to ještě SELČ nebo už SEČ.

Pak právě nastávají třeba ty problémy se změnou času, protože když vám přijde čas 27. 10. 2019 02:30:00, tak nevíte, jestli je to ještě SELČ nebo už SEČ.

Eh, proto právě jviki psal, že je třeba používat timezone, která přechodem letní/zimní čas netrpí. Použiju-li běžnou timezone, tak naopak ten záznam není jednu hodinu v roce unikátní a ještě k tomu matematicky rostoucí.

+1 na předchozí příspěvek, v dnešním globalizovaném světě jsou timezone v DB silně kontraproduktivní, v podstatě je typicky nevhodné je řešit i na aplikačním serveru (backend) a celou konverzi dělat až na úrovni browseru. Samozřejmě tam, kde to jde (moderní single page aplikace). Výjimkou jsou různé účetní reporty (nebo cokoliv, co má návaznost na legislativní pravidla), ale tam se stejně udržuje uměle vytvořené razítko spíš s datem než s časem. Je-li tam čas, tak je stejně timezone natvrdo zadrátovaná na úrovni vlastnické entity než k záznamu samotnému.

On je v těch časových zónách trochu zmatek, existují dva typy časových zón – „občanské“, jako třeba Europe/Prague, které přechodem na zimní čas a zpět trpí. A pak geografické, jako UTC, SEČ a SELČ, které tím přechodem netrpí. PostgreSQL používá jenom ten druhý typ zón.

I pokud použijete pro celou databázi jednu časovou zónu, třeba UTC, pořád je vhodné data do databáze ukládat i s tou časovou zónou, protože pak máte tu informaci přímo v databázi a nemusíte ji přidávat externě. Když použijete datový typ bez časové zóny, interpretuje databáze časovou zónu na základě konfigurace, což je obvykle lokální zóna, u nás tedy typicky Praha. Za prvé tam máte zase problém přechodem z letního času, ale hlavně riskujete, že budete chtít server přesunout do USA nebo tam udělat repliku, nastavíte lokální čas třeba na východní pobřeží, a všechny časy se vám o několik hodin posunou, protože jste změnil tu implicitní časovou zónu.

Ne, zahazovat údaje, které znám (např. tu časovou zónu) není nikdy dobrý nápad. A v globalizovaném světě je používání času bez časové zóny o to horší nápad.

Bacha na to - V postgresu jsou k dispozici oba způsoby určení zóny (plus další) - na venek - interně jede v UTC

postgres=# show timezone;
┌───────────────┐
│   TimeZone    │
╞═══════════════╡
│ Europe/Prague │
└───────────────┘
(1 row)

A pak geografické, jako UTC, SEČ a SELČ, které tím přechodem netrpí. PostgreSQL používá jenom ten druhý typ zón.

Zde matete lidi. Jsou dvě různé věci:
1. V datovém typu TIMESTAMP WITH TIME ZONE ukládá postgres časovou informaci v univerzálním formátu (zjednodušeně řečeno, interně je čas uložen na nultém poledníku) + k tomu uloží ofset v hodinách od nultého poledníku (tj. např. +1 v případě, že v době uložení platí SEČ, +2 v případě, že v době uložení platí SELČ).

2. Naopak databáze samotná pracuje s těmi "občanskými" timezonami. V nastavení postgresql.conf (ale je to konfigurovatelné i za běhu pomocí SET timezone='Euro­pe/Prague'; To nemění nic na databázovém serveru, ale jen klientské nastavení. To, kde se nachází ten, kdo s databází pracuje. Díky tomuto nastavení pak postgres ví, jak má čas uložit do databáze.

Příklad:
a) pokud mám nastaveno timezone='Euro­pe/Prague' a dám příkaz
SELECT CAST('2019-01-01 00:00:00' AS TIMESTAMP WITH TIME ZONE);
dostanu: '2019-01-01 00:00:00+01'

b) pokud mám nastaveno timezone='Euro­pe/Moscow' a dám příkaz
SELECT CAST('2019-01-01 00:00:00' AS TIMESTAMP WITH TIME ZONE);
dostanu: '2019-01-01 00:00:00+03'

No a nakonec, pokud tyto dvě "novoroční půlnoci" od sebe odečtu:
SELECT CAST('2019-01-01 00:00:00+03' AS TIMESTAMP WITH TIME ZONE) - CAST('2019-01-01 00:00:00+01' AS TIMESTAMP WITH TIME ZONE);
dostanu: '-02:00:00' - tedy přesně dvouhodinový rozdíl mezi pražským a moskevským Novým rokem.

Pokud si v aplikaci zajistíte převod do UTC, tak pak TimestampTz nepotřebujete. Pokud ale ne, tak TimestampTz je nutný, právě třeba kvůli přechodům standardní, letním časem. Hodně také záleží, odkud berete čas. Pokud používám transakční čas (z db serveru), tak k žádným problémům nemůže dojít. Pokud používám klientské časy, tak se musím spolehnout (ve všech případech), že mi poskytne správný čas (případně správnou zónu).

K nastavení zón - právě u TimestampTz vůbec nikdo nečeká, že mají klienti nastavené STEJNÉ zóny. Jediným požadavkem je, aby měli nastavené zóny SPRÁVNĚ. Pokud správně nastavené nebudou, tak to, samozřejmě, fungovat nemůže. Pokud pracujete čistě s UTC, tak ok, ale pak veškerou práci s převody času si musíte ošetřit sám.

U "timestamp with time zone" jsme se u jedné aplikace trochu spálili, nebo jsme spíš narazili na ne zcela jednoznačnou dokumentaci. Potřebovali jsme ukládat časové značky, které dodávala klientská strana, a zároveň ke každé značce timezone, kterou také dodal klient. Z dokumentace postgresu se nám tehdy zdálo, že přesně toto "timestamp with time zone" - tedy že _ukládá_ timestamp vč. informace o původní TZ, se kterou byl vložen. Ale skončili jsme s tím, že tomu tak není. Jediný rozdíl proti "timestamp without time zone" je, že při výstupu je hodnota přepočtena na TZ nastavenou pro danou session. Nebo je to jinak?

VS: PostgreSQL ukládá typ s časovou zónou vždy v UTC. Informace o tom, která časová zóna byla na vstupu, se tedy ztrácí – pokud chcete vědět, zda daný časový okamžik uživatel zadal jako 1:00 SELČ nebo 0:00 UTC, musíte si tu uživatelem zadanou zónu poznamenat jinak. Ale díky tomu, že u uloženého času známe časovou zónu, může se kdykoli čas převést do jakékoli jiné časové zóny. Naproti tomu typy bez timezone se interpretují tak, že jde o čas v lokální časové zóně dané session. Takže přesunete server jinam a nastavíte místní časovou zónu, a hned máte všechny časy jinak.

Z hlediska dat uložených v záznamech mezi těmi tedy není rozdíl, rozdíl je v tom typu – jeden říká, že uložený čas je i s časovou zónou a při načtení se jen rozhodne, do které časové zóny ho chcete konvertovat. Druhý typ je uložený bez časové zóny a při načítání se nerozhoduje, kam se konvertuje (tj. přepočítá), ale do které časové zóny se vloží – tj. čas se nezmění, akorát se dodatečně rozhodneme, zda je to čas v Praze, v Londýně nebo v New Yorku. Je to podobné, jako kdybyste ukládal měnu – a buď byste jí vždy ukládal v USD a při vstupu a výstupu přepočítával na jiné měny správným kurzem (akorát by všechny kurzy musely být neměnné), nebo byste měnu ukládal jenom jako číslo, a teprve při načtení byste se rozhodl, zda to budete považovat za USD, CZK nebo ITL.

Analogie s měnami mne nenapadla, ale je dobrá - je to hodně podobné. Když bych použil tuto analogii, tak Posgres obsahuje databázi kurzů, která se průběžně aktualizuje.

To narážíte na další častý nešvar programátorů. Nepoužívají transakce a ani nevědí o neměnnosti now() v rámci transakce. V důsledku toho programátor používá místní čas aplikačního serveru a cpe timestampy z aplikace do sekvence příkazů. Tím si zajistí neměnnost timestampu, bohužel je to pak ale závislé na tom, aby místní časy byly nastaveny stejně a správně. Bohužel, oblíbené databázové abstrakční frameworky programátora vysloveně vedou k tomu, aby nepoužíval transakce a k tomu, že mu "jednoduchým" způsobem a hlavně bez přemýšlení časové údaje předžvýkají.

Kdy použít transakce a kam vůbec data ukládat, nechám na programátorech, kteří lépe rozumí business logice dané funkcionality.

S tím now() - jaký je rozdíl mezi databázovým now() a aplikačním serverem? Transakce se může zablokovat v různé fázi a stejně nemám žádnou garanci, který timestamp se kdy použije, klidně to může skončit vložením dvou řádků s klesajícím timestamp. Používat now(), ať už z databáze nebo aplikačního serveru k něčemu kritickému je v principu nesmysl. Je-li to nějaký klíčový identifikátor, stejně pochází obvykle zcela odjinud, například z koncového IoT zařízení, tedy nejen mimo transakci, ale zcela asynchronní vstup z hlediska aplikačního serveru i databáze.

Nevím, které databázové abstrakční frameworky programátora vedou k nepoužívání transakce, EJB, Spring i Hibernate při zápisu transakce přímo vyžadují.

Každá rozumná databáze vrací v now() čas začátku transakce, tj. v průběhu transakce se ta hodnota nemění a všechny záznamy vložené/aktua­lizované během té transakce budou mít stejný časový údaj.

Občas znalosti programátorů jsou tragický - software se nebrání, a většinou není vidět, jak je špatně naprogramovaný. Teď jsem potkal kluky, kteří po letech programování zjistili, že v databázi jsou race condition ošetřené, a že IF NOT EXISTS vůbec nic neřeší.

Bohužel dobrá výuka programování na středních školách chybí, ale to je právě ta doba, kdy se programátoři "rodí". Ti jsou pak odkázaní na samonauku. A přiznejme si, co najdou: příklady v PHP a MySQL, a pokud někde narazí na BEGIN, není pro ně důležité ani načíst si, co to to znamená.

Pak si také myslím, že svoji roli hraje úroveň dokumentace. MySQL má dokumentaci pochopitelně kvalitní a kompletní, ale málo edukativní. PostgreSQL má dokumentaci mnohem lepší. Bohužel, začínající programátoři se k postgresu nedostanou, a ve chvíli, kdy se dostanou, mívají už většinou zkreslené představy o tom, co mají od databáze očekávat.

Kapitolou samou o sobě je naučit programátory vnímání doby trvání příkazů a transakcí. Programátorovi přijde půl sekundy jako vynikající výsledek, pár sekund jako přijatelný. Vysvětlit mu, že ale s nárustem dat a s nárustem provozu a paralelních požadavků najednou latence naskáčou i na desítky sekund, je nadlidská práce. Mají svoji hlavu a "zkušenost" s provozem zájmových stránek sběratelů korkových zátek, a nevymluvíte jim, že může existovat i trochu jiný typ provozu a zátěže.

Smutné je, že namísto toho, aby se programátoři zaměřovali na databáze, které jsou právě určeny k nejlepšímu zpracování dat, tráví se spousta času na vymýšlení různých aplikačních cache. Podívejte se do pár nejběžnějších frameworků. Databázové třídy jsou univerzální od SQLite, přes MySQL a Postgres, až po MS SQL a Oracle. V takovém případě je jakákoliv optimalizace opravdu prakticky nemožná a nezbývá, než data zpracovat v aplikaci. Programátor pak přijde k hotovému, a v podstatě už ani nemá možnost se práci s databází naučit.

Zcela souhlasim s jviki.
Vsechno ulozit jako UTC BEZ timezone.
(pokud potrebuji znat timezone, je to v dalsim sloupecku napr. "Europe/London" ale to je potreba jen velice vyjimecne)

Vyskledkem je pracovni prostredi, ve kterem je minimum problemu:
- vzdy do DB ukladam v UTC (konverze nutna na urovni aplikace pred zapisem do DB)
- vzdy z DB ctu v UTC (konverze vetsinou nutna nma urovni aplikace po cteni z DB)
- jednotlive DB jsou jednoduse propojitelne
- kazda aplikace je nucena ke konverzi timezone pred zapisem a po precteni

Ano, da se pracovat i s hodnotami "with timezone" jenze je to podle me v realnem svete k nicemu. Proc?
Protoze v realnem svete je to trochu jinak.

Budto timezone vubec k nicemu neni (to je 99% pripadu) takze ukladat den_vydani_clanku v Nemecku v Europe/Berlin a v anglii v Europe/London je vam k nicemu. Proc? Protoze to je jedna jasne urcena hodnota v UTC - tak to proste stejne ulozite v UTC. A pokud to chcete zobrazit japoncum, zkonvertujete z UTC do Asia/Tokyo a je to hotovo.

Jsou situace, kdy CHCETE znat v jake casove zone ma to datum byt zobrazovano. Napr. u letenek se zobrazuje cas odletu / priletu vzdy podle polohy letiste. Potom je dobre mit ulozeny cas v UTC a zaroven budto nejakym slozitejsim kusem kodu odvodit co je na tom kterem miste za casovou zonu (coz by slo) anebo se proste udela dalsi sloupecek a casova zona se da tam. Aplikace potom musi precist oba sloupce a formatovat podle toho kde je info o casove zone.

Pro pokrocilejsi use case si obcas pomaham pomocnymi tabulkami. Nekdy se hodi tabulky s cisly tydnu (mnoho zemi ma mnoho ruznych cislovani tech standardu je snad 8), nekdy se hodi pomocne tabulky i pro groupovani po dnech nebo tydnech (napr. kvuli statistikam v ruznych zemich).