Vlákno názorů k článku Změna času je zase tu. Změna, kterou vlastně vůbec nepotřebujeme od dfsdfsdf - nejslabsi mate padaka!

Zóna se týká ČR, protože jsme na českém serveru, tak mi ČR přišla jako dobrá volba. Uloženo je to pořád ještě jako datum, čas a časová zóna +1 nebo +2, a hádám, že to tak ještě nějakou dobu vydrží. Ale kdyby se to změnilo, neprodleně vám to nahlásím.

Bez indexů 20 123 ms ± 823 ms a 20 458 ms ± 691 ms.

Pokud byste zároveň měl jistotu, že se státy nestěhují mezi časovými zónami, což také neplatí.

A na druhou stranu, pokud by se letní/zimní čas střídal stejně ve všech časových zónách (včetně UTC), můžete v klidu vyhledávat podle UTC, ať už vás zajímá UTC nebo lokální čas.

Datový typ timestamptz je datum a čas s časovou zónou. Uložené je to tedy s časovou zónou, konkrétně s časovou zónou, která byla uvedena u té konkrétní hodnoty při vkládání. Ostatně, můžete to vidět sám, v tom výpisu je ten sloupec ts vypsaný a vidíte tam dva řádky s časovou zónou +01 a dva s +02.

Údaje jsem vkládal s časovou zónou Europe/Prague, jak je z výpisu vidět. Tento zápis se v okamžiku konverze řetězce na datum a čas s časovou zónou převede na odpovídající časovou zónu +01 nebo +02, podle toho, kam ta která hodnota spadá.

Když použiju časovou zónu Europe/Prague, můžu sedět klidně na Měsíci a počítač, ze kterého jsou data zadaná, může být třeba na Marsu, a pořád to bude časová zóna označující lokální čas v ČR.

Už se stalo. Tou sekvencí příkazů jsem myslel hlavně to, že je to tabulka bez indexu. Ten insert měl samozřejmě milion záznamů (přesně, byla to nová session s novou temporary tabulkou).

Když mám index podle UTC, je mi samozřejmě k ničemu, když chci hledat podle lokálního času. A opačně, když mám index podle lokálního času, je mi k ničemu, když chci hledat podle UTC. To ale není problém střídání letního a hvězdného času, ale toho, že chcete hledat podle dvou různých kritérií. Ten problém se vyřeší úplně stejně zrušením letního času jako zrušením vyhledávání podle UTC.

Jinak to, že jsou ty indexy navzájem „k ničemu“ není tak docela pravda, ony by se daly využít i navzájem, akorát by se vyhledávaný interval rozšířil o hodinu výsledná sada záznamu by se následně profiltrovala podle časové zóny. Bylo by to sice méně efektivní, než přímé využití indexu, ale pořád ještě efektivnější, než fullscan. Ale pochybuju, že by na tohle přišel nějaký optimalizátor „sám od sebe“, bylo by nutné ten dotaz odpovídajícím způsobem přeformulovat.

Čas zpracování pro sekvenci příkazů tak, jak jsem je napsal (bez indexů) je pro oba dotazy až na statisticky nevýznamnou odchylku shodný a trvá to dlouho, protože se musí provést fullscan celé tabulky.

Čas zpracování po přidání indexů na lokální čas a na UTC čas je pro oba dotazy až na statisticky nevýznamnou odchylku shodný a dotazy jsou provedeny velmi rychle, protože se vyberou řádky splňující podmínku podle indexu.

Konkrétní časy nemá smysl sem psát, protože záleží na tom, jak výkonný je počítač, na kterém databáze běží, na její konfiguraci i na tom, jestli počítač není zatížen i něčím jiným.

V komentáři jsem vám napsal SQL příkaz pro definici tabulky, kde jsou uvedené datové typy, příkazy, kterými byla data vložena (včetně hodnot), a dotazy včetně jejich výstupů, kterými jsem data získal. Zaznamenal jste doufám, že redakční systém Lupy ty příkazy vypisuje do malého okna a je potřeba si odrolovat dolů, abyste je viděl celé.

Je na těch triviálních SQL příkazech něco, co vám mohu dovysvětlit? Mám nějak upřesňovat, že zápis '2016-03-28 15:30:00 Europe/Prague' znamená čas 15:30 dne 28. 3. 2016 v lokální časové zóně ČR?

Aha, takže „index pro čas a časovou zónu“ není určení, jak má index vypadat.

Podstata mé odpovědi je také úplně někde jinde, než konstrukce indexu. Podstatou je to, že když chcete v databázi něco efektivně vyhledávat, musíte k tomu mít vhodně zkonstruovaný index. Přičemž pokud chcete vyhledávat podle lokálního času, potřebujete index nad lokálním časem – bez ohledu na to, jakým způsobem jsou data uložena. Výhodou některých databází pak je to, že takový index vytvoříte použitím jedné vestavěné funkce.

OK, tak ještě jednou.

postgres=#CREATE TEMPORARY TABLE log (id int PRIMARY KEY, ts timestamptz);
CREATE TABLE

postgres=# INSERT INTO log (id, ts) VALUES
  (1, '2016-03-26 15:30:00 Europe/Prague'),
  (2, '2016-03-26 16:30:00 Europe/Prague'),
  (3, '2016-03-28 15:30:00 Europe/Prague'),
  (4, '2016-03-28 16:30:00 Europe/Prague');
INSERT 0 4

postgres=# SELECT *, ts AT TIME ZONE 'UTC' AS ts_utc FROM log;
 id |           ts           |       ts_utc
----+------------------------+---------------------
  1 | 2016-03-26 15:30:00+01 | 2016-03-26 14:30:00
  2 | 2016-03-26 16:30:00+01 | 2016-03-26 15:30:00
  3 | 2016-03-28 15:30:00+02 | 2016-03-28 13:30:00
  4 | 2016-03-28 16:30:00+02 | 2016-03-28 14:30:00
(4 rows)

postgres=# SELECT *, ts AT TIME ZONE 'UTC' AS ts_utc FROM log WHERE ts::time BETWEEN '16:00:00' AND '17:00:00';
 id |           ts           |       ts_utc
----+------------------------+---------------------
  2 | 2016-03-26 16:30:00+01 | 2016-03-26 15:30:00
  4 | 2016-03-28 16:30:00+02 | 2016-03-28 14:30:00
(2 rows)

Čemu na tom nerozumíte?

Akorát předvádíte, že netušíte, o co jde. Třeba bod 3 – když budete mít vše jenom podle UTC a budete potřebovat najít všechny záznamy, které mají čas mezi 16:00 UTC a 17:00 UTC – budete pro efektivní hledání potřebovat index pouze s časem. Protože hodiny, které vás zajímají, jsou uprostřed údaje datum+čas, takže index na datum-čas by vám byl k ničemu. Bez ohledu na časové zóny.

Uložené mám datum a čas s časovou zónou, vždy podle lokální zóny používané v okamžiku toho daného času – údaje „od listopadu do března“ tedy budou uložené s CET, údaje „od dubna do října“ s CEST. Jak to interně ukládá databáze mne nemusí zajímat. Konverze je to ::time, to je přetypování na čas bez časové zóny, tedy '17:00:00 CET'::time = '17:00:00', '17:00:00 CEST'::time = '17:00:00'. Vaše podezření je špatné, PostgreSQL umí s časem počítat správně.

Vy jste vubec nepochopil v cem je problem..
Já bych to trochu upřesnil. Já nechápu, proč vy děláte problém z něčeho, co vůbec problém není. Ptal jste se na ten hrozně problémový dotaz, tak jsem vám ho o pár komentářů níže napsal – po pravdě řečeno, neexistuje mnoho dotazů, které by byly jednodušší, než tento.

Mam li datum-cas v UTC, tak casovou zonu ukladat nepotrebuji. K cemu by mi byla kdyz ji znam?
Pro případ, když chcete uživateli zobrazit datum a čas ve stejné časové zóně, ve které je zadal. Třeba když uživatel v ČR zadá začátek události jako 26. 3. 2016 02:00:00 GST, asi to bude chtít příště zobrazit jako 26. 3. 2016 02:00:00 GST, a ne jako 25. 3. 2016 22:00:00 UTC, ani jako 25. 3. 2016 23:00:00 CET.

K cemu je vam cas interne prevedeny do UTC, kdyz chcete najit vsechny udalosti ke kterym doslo mezi 17 a 18 hodinou libovolneho dne a hlavne, mistniho casu?
Asi jste myslel datum a čas převedený do UTC. Je mi dost k ničemu, stejně jako mi bude k ničemu i v případě, že budu hledat údaje mezi 17 a 18 hodinou UTC daného dne. Když potřebuju vyhledávat podle času, musím mít v indexu čas – když budu vyhledávat podle času v UTC, budu tam mít čas v UTC, když budu vyhledávat podle lokálního času, budu tam mít lokální čas.

Nikoliv, datum-cas ukladaji v datetime ve formatu UTC. Zadnou casovou zonu.
UTC není formát. UTC je časová zóna.

Snazite se buhviproc obhajit neobhajitelne a pomalu u toho zacinate pusobit trosku srandovnim dojmem,:-)
U psaní toho triviálního SQL dotazu jsem se opravdu bavil.

SELECT * FROM log WHERE log.timestamp::time BETWEEN '17:00:00' AND '18:00:00'

Protože chcete prohledávat podle času, potřebujete mít na čas index (a s časovými zónami to nijak nesouvisí, stejné by to bylo, kdyby neexistovalo nic jiného, než UTC ¨je to jako kdybyste chtěl vyhledávat třeba podle textového řetězce od pátého znaku dál). Takže si v tomto případě uděláte index na log.timestamp::ti­me a výše uvedený dotaz se vyhodnotí prostě jako range podle idnexu.

Jinak MSSQL je nejcaste pouzivany DB server v enterprise kategorii.
Což ještě nutně neznamená, že se v něm pohodlně pracuje s datem a časem.

Nevim jestli ma vyznam abych vysvetloval ze offset opravdu neni casova zona a ze se pouziva k necemu malinko jinemu.
Asi to význam nemá, když samotný Microsoft v dokumentaci uvádí: „Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.“

Kazdopadne by bylo dobre mit na zreteli, ze se pro ukladani casu celkem logicky pouziva k tomu urceny datetime.
Pokud si vy ukládáte datum a čas bez časové zóny, nemůžete se pak divit, že ji uloženou nemáte. Hlavně to nezačněte ukládat i bez času, pak byste začal tvrdit, že čas v rámci dne je nesmysl, protože vy s tím máte problémy v databázi, a měl by stačit jen kalendář.

Treba Dynamics ktery tam sklada cas v UTC formatu a prevod na localtime provadi az na vystupu.
Aha, takže ukládají čas i s časovou zónou. Informace „vše je v UTC“ je také způsob uložení časové zóny.

Je to tak proto, ze konverze casu je proste draha operace, zejmena kdyz do veci vnasi hokej stridani zimniho a letniho casu.
Právě proto DB interně pracuje jen s časem v UTC, a když jí v dotazu zadáte jinou časovou zónu, nejprve si to interně převede do UTC. Což je levná operace, je to jeden dotaz do interní tabulky a provede se jednou pro celý dotaz.

Pokud máte možnost zvolit správný datový typ a zvolíte špatný, je to problém váš. Pokud používáte špatnou databázi, je to problém té databáze a také toho, kdo ji zvolil. Ale těžko to může být obecný problém práce s datem a časem, když existují databáze, které s tím umí normálně pracovat. Např. v PostgreSQL můžete porovnávat datum a čas v různých časových zónách, můžete převádět datum a čas z jedné časové zóny do jiné, není s tím žádný problém. A upřímně řečeno, ono na tom není nic složitého, i do toho MS SQL byste si to mohl snadno doprogramovat. Prostě stačí vše převádět do UTC, a vedle uložené hodnoty v UTC si ještě uložit časovou zónu, pro případ, že bude uživatele zajímat, v jaké časové zóně si ten údaj uložil.

Ano, MS SQL je specifická databáze. Nicméně datetimeoffset je právě datový typ, který umožňuje ukládat datum a čas s časovým pásmem.

To, co vy složitě popisujete jako jakési podivné posunování času, je ve skutečnosti to, co jsem napsal já – změna časového pásma, které se používá pro lokální čas. V zimě používáme pro lokální čas časové pásmo SEČ (středoevropský čas, mezinárodní zkratka CET), v létě používáme časové pásmo SELČ (středoevropský letní čas, mezinárodní zkratka CEST).

Pokud si čas ukládáte i s časovým pásmem, žádný problém nenastává. Protože SEČ na UTC převedete jednoduše tak, že odečtete jednu hodinu, SELČ na UTC převedete tak, že odečtete dvě hodiny. Špatná kombinace nastat nemůže. Problém máte jedině tehdy, když ukládáte datum a čas bez časové zóny, což je váš problém.

Pokud vaše databáze nemá vestavěné funkce pro rozumnou práci s datem a časem, je to problém té databáze. Třeba PostgreSQL umí s datem a časem s časovým pásmem normálně pracovat. Záznamy v databázi můžete mít uložené klidně v UTC nebo v CET/CEST. Když si vytvoříte SQL dotaz, kde použijete čas 17–18 hodin v časové zóně Europe/Prague, v době kompilace dotazu se to převede na UTC a pak se to v databázi vyhledá normálně podle indexu.

Když píšete o indexu na sloupci, předpokládám, že myslíte nějakou databázi. Sloupec v té databázi ale má datový typ, asi datum a čas s časovou zónou. Ten převod pak zařídí databáze a použije index, protože převod mezi časovými zónami je jednoznačně dán, a kterékoli dva časové údaje i v různých časových zónách dokážete jednoznačně seřadit.
Pokud někdo ukládá datum a čas bez časové zóny, je to jeho problém.

Čas se „posouvá zpět“ pouze v případě, že neuvádíte kompletní časový údaj (vynecháte časovou zónu). Jinak s tím problém není, protože časové údaje s uvedením časové zóny jsou jednoznačné a jsou na sebe navzájem bez problémů převoditelné. 02:30:00 SEČ = 03:30:00 SELČ = 01:30:00 UTC. Celý „problém“ pak spočívá v tom, že např. v EU se v období do 27. 3. 2016 00:59:59 UTC používá SEČ, od 27. 3. 2016 01:00:00 UTC do 30. 10. 2016 00:59:59 UTC se používá SELČ.