Datové typy pro práci s datumem a časem v PostgreSQL
Autor: Pavel Stěhule
Před vánocemi mne jeden ze čtenářů požádal o doplnění mého taháku o informace ohledně práce s časem v Postgresu. To mne trochu překvapilo. O tom, že práce s časovými zónami není úplně intuitivní jsem věděl, a párkrát jsem u firem řešil problém s přechodem letní/zimní čas, nicméně to byly všechny problémy, na které jsem v této oblasti narazil, a všechny vlastně byly triviální. Pro jistotu jsem si ještě jednou prošel dokumentaci, a musím uznat,že na pár temných zákoutí jsem narazil, a v následujícím textu se je budu snažit popsat a osvětlit.
| Date | dny | 4 bajty | od 4713BC .. 5874897 AD |
| Time | čas | 8/12 bajtů | 00:00:00 .. 24:00:00 |
| Timestamp | den-čas | 8 bajtů | od 4713 BC .. 294276 AD |
| Interval | interval | 16 bajtů | ±178M let |
Postgres pro práci s časem používá čtyři typy: date, time, timestamp a interval. Když se podíváte na následující
tabulku, tak doufám, že se mnou souhlasíte, že jsou nadimenzovány více než dostatečně. Žádné opakování problému
roku 2000 nehrozí.
Date
Rozdíl dvou hodnot typu date je celé číslo (počet dnů). Rozdíl dvou hodnot typu timestamp je hodnota typu interval.
Pro jednoduché operace, kde se pracuje se dny, se používá typ date a jednoduchá celočíselná aritmetika. Pro ostatní
operace je tu timestamp a interval. Zde žádné temné zákoutí nevidím, a tento model mi odjakživa vyhovoval (a celočíselný
typ date jsem v ostatních vývojových prostředích postrádal).
Horší je to už se zápisem hodnot. Tady už je vidět stáří Postgresu (např. podpora formátů ještě z dob před implementací
SQL), archaické nasazení SQL (kdy se předpokládalo, že SQL zapisuje uživatel nebo že aplikace, která zobrazuje data, je
hloupá a neumožňuje změny formátů zobrazených dat), případně kdy Postgres koketoval s myšlenou kompatibility s Oraclem
(funkce to_char, to_date). Datum, interval je možné zapsat na sto různých způsobů, ačkoliv by pohodlně stačil jeden jediný.
Naštěstí kód, který obsluhuje vstup hodnot zmíněných typů je kompletní, stabilní a náklady na jeho údržbu jsou dnes minimální.
Typ date používáme pro uložení dne - např. 21.2.2019. Interně je to integer (celé číslo), které udává počet dnů k/od počátku,
čímž je v tomto případě 1. ledna roku 2000. Přičtením n se posouváme v čase vpřed o n dnů, naopak odečtením n se posouváme v čase zpět.
postgres=> SELECT current_date, current_date + 1 AS zitra, current_date - 1 AS vcera; ┌──────────────┬────────────┬────────────┐ │ current_date │ zitra │ vcera │ ╞══════════════╪════════════╪════════════╡ │ 2019-02-21 │ 2019-02-22 │ 2019-02-20 │ └──────────────┴────────────┴────────────┘ (1 row)
Aktuální den vrací (pseudo) funkce current_date.
Jak už jsem předeslal, podporovaných vstupních formátů je více než málo. Preferovaným formátem by měl být tzv ISO formát
YYYY-MM-DD (případně YYYYMMDD). Tento formát je jednoznačný. Naopak zde používaný (v PostgreSQL označovaný jako German nebo DMY)
formát DD-MM-YYYY (den-měsíc-rok) by se používat neměl, protože zde hrozí záměna z formátem MDY (měsíc-den-rok). To, jak Postgres
interpretuje hodnoty závisí na nastavení konfigurační proměnné datestyle. Je dobré psát aplikace jejichž chování pokud možno
co nejméně závisí na konfiguraci Postgresu. Použití jiného než ISO formátu má smysl, pokud uživatel ručně zapisuje příkazy,
a je normální, rozumné a praktické, mu umožnit zápis ve formátu, na který je zvyklý. Naopak, pokud SQL je generováno aplikací,
nemá smysl používat jiný než ISO formát.
| 2019-02-21 | ISO formát |
| 21-02-2019 | DMY |
| 02-21-2019 | MDY |
| 2019.052 | rok a den v roce |
| J2458199 | číslo dne v Juliánském kalendáři |
Poslední dva formáty jsem nikde jinde než v dokumentaci neviděl.
Typ date podporuje speciální konstanty infinity a -infinity.
Time
Pro uložení času bez informace o dni je tu typ time. Jedná se o 8 bajtovou hodnotu (fakticky bigint), kde se čas zakóduje
jako počet mikrosekund od počátku, v tomto případě 0:0:0. Typ time existuje také ve formě se zakódovanou časovou zónou. V tom
případě má hodnota 12 bajtů. Jelikož časové zóny závisí na datumu, které v tomto případě nemáme, tak se nedoporučuje tento
typ používat. Zápis hodnoty typu time může být buďto s oddělovačem ':' nebo bez oddělovače:
| 12:00:00.0000 | poledne |
| 120000.0000 | poledne |
Timestamp
Zkombinováním typu date a time vznikne typ timestamp. V něm jsme schopni specifikovat určitý čas určitého dne. V Postgresu
typ timestamp má 8 bajtů, a to jak ve variantě s časovou zónou (ta se neukládá), tak ve variantě bez časové zóny. Hodnota
je uložena jako počet mikrosekund ku počátku, což je opět 1.1.2000. U těchto typů můžeme použít zkrácené názvy:
timestamp pro timestamp without time zone a timestamptz pro timestamp
with time zone.
Typ timestamp with time zone (s časovou zónou) si zaslouží podrobnější popis. Jeho použití není ve všech situacích
intuitivní. Ačkoliv by název napovídal něco jiného, zadaná časová zóna se nikam neukládá. Místo toho se ukládaná časová
hodnota převádí do časové zóny UTC (Londýn). Při zobrazení se vždy provede konverze z časové zóny UTC do časové zóny
klienta (viz konfigurační proměnná timezone). Při uložení se odečte interval odpovídající zadané časové zóně (případně
se použije časová zóna klienta). Při zobrazení se naopak přičte. Pokud se díváme na hodnotu typu timestamp with time zone,
tak vždy uvidíme lokální čas (a to ať v minulosti, přítomnosti nebo budoucnosti).
Aby to nebylo jednoduché - interval časové zóny (posun vůči univerzálnímu času UTC) se pro danou lokalitu může měnit např. v ČR letní čas, zimní (standardní) čas. Z interní databáze se pro specifikovanou lokalitu, rok a den v roce dohledává interval, který platil, je platný nebo bude platit a použije se pro kalkulaci jak při uložení hodnoty, tak při jejím zobrazení.
postgres=> SHOW timezone; ┌───────────────┐ │ TimeZone │ ╞═══════════════╡ │ Europe/Prague │ └───────────────┘ (1 row) -- v únoru je ještě zimní +1 čas postgres=> SELECT '21.2.2018 18:00:00'::timestamp with time zone; ┌────────────────────────┐ │ timestamptz │ ╞════════════════════════╡ │ 2018-02-21 18:00:00+01 │ └────────────────────────┘ (1 row) -- v květnu je se používá letní (standardní) +2 čas postgres=> SELECT '5.5.2018 18:00:00'::timestamp with time zone; ┌────────────────────────┐ │ timestamptz │ ╞════════════════════════╡ │ 2018-05-05 18:00:00+02 │ └────────────────────────┘ (1 row) postgres=> SELECT current_timestamp; ┌───────────────────────────────┐ │ current_timestamp │ ╞═══════════════════════════════╡ │ 2019-02-21 16:43:47.624805+01 │ └───────────────────────────────┘ (1 row)
Výběr intervalu časové zóny se neřídí aktuálním dnem, ale dnem ukládané nebo zobrazené hodnoty. Pro typ
timestamp with time zone se hodnota ukládá v UTC (normalizuje) a zobrazuje se lokálně. Jak normalizace
tak zobrazení je vztažené vůči zobrazené hodnotě (roku a dnu) a lokalitě. Výhodou uložení hodnoty
vztažené k UTC (koordinovanému světovému času) je skutečnost, že tento "čas" nemá letní/zimní posuny,
a tudíž nemůže docházet k fantomům, že by zmizela hodina, nebo jedna hodina by byla 2x. Proto se doporučuje
pro uložení časových hodnot typu timestamp vždy použít typ timestamp with time zone. Bohužel přívlastek
"with time zone" není výchozí. Pokud použijete v definici tabulky pouze "timestamp", tak jste vytvořili
položku typu timestamp without time zone.
U typu timestamp without time zone k těmto výše popsaným transformacím nedochází. Zapsaná hodnota se uloží a zobrazí,
tak jak byla zapsána. To samozřejmě způsobuje problémy při změně času z letního na zimní (interval +2 vůči
koordinovanému světovému času - UTC), a zimního na letní (interval +1 vůči UTC). Z tohoto důvodu se nedoporučuje
typ timestamp without time zone používat v tabulkách. Tento typ má svůj význam a použití, ale nepoužívejte
jej v příkazech CREATE TABLE, ALTER TABLE.
Také typ timestamp podporuje hodnoty infinity nebo -infinity.
Docela častou otázkou je zobrazení hodnoty typu timestamp pouze na sekundy. U tohoto typu můžeme definovat
přesnost - timestamp(0) je čas pouze v sekundách, timestamp(6) je výchozí formát - čas v mikrosekundách:
postgres=> SELECT current_timestamp, current_timestamp::timestamp(0) with time zone; ┌───────────────────────────────┬────────────────────────┐ │ current_timestamp │ current_timestamp │ ╞═══════════════════════════════╪════════════════════════╡ │ 2019-02-24 07:31:38.559803+01 │ 2019-02-24 07:31:39+01 │ └───────────────────────────────┴────────────────────────┘ (1 row)
Přesnost u timestampu samozřejmě můžeme definovat i u DDL příkazů.
Interval
Tak jako se k typu date přičítá, odčítá celé číslo, abychom se mohli posouvat na časové ose, tak se k
typu timestamp přičítá, odčítá typ interval. Tento typ je jedním z nejkomplexnějších vestavěných typů
v PostgreSQL. Interně se jedná o strukturu se třemi položkami - pro měsíce, dny a sekundy. Pro pochopení
chování typu interval je nutné si zapamatovat, že pracujeme s kompozitním typem. Každá položka se nastavuje
zvlášť a systém sám o sobě nepřesouvá hodnoty mezi těmito položkami. Ono to v mnoha případech ani jednoduše
nejde. Asi by bylo možné specifikovat kolik vteřin má den, ale už nemůžeme jednoznačně určit kolik dní má
měsíc. A proto se s měsíci kalkuluje zvlášť, se dny se kalkuluje zvlášť a zrovna tak se sekundami.
postgres=> SELECT interval '300 days 48 hours'; ┌───────────────────┐ │ interval │ ╞═══════════════════╡ │ 300 days 48:00:00 │ └───────────────────┘ (1 row)
Všechny ostatní jednotky (roky, hodiny, minuty) se do zmíněných třech základních položek přepočítávají a sčítají:
postgres=> SELECT interval '12 month 1 year 3600 sec 60 min'; ┌──────────────────┐ │ interval │ ╞══════════════════╡ │ 2 years 02:00:00 │ └──────────────────┘ (1 row)
Případně odečítají:
postgres=> SELECT interval '1 month -1 day -1 hour +7200 sec'; ┌─────────────────────────┐ │ interval │ ╞═════════════════════════╡ │ 1 mon -1 days +01:00:00 │ └─────────────────────────┘ (1 row)
Zajímavě (a prakticky) je implementováno přičítání měsíců (interval) k timestampu. Pokud to je možné, tak
dostaneme den se stejným pořadovým číslem v následujícím měsíci nebo poslední den v měsíci. Nikdy se nedostaneme
do přespříštího měsíce:
postgres=> SELECT '2019-01-31'::timestamp + interval '1month'; ┌─────────────────────┐ │ ?column? │ ╞═════════════════════╡ │ 2019-02-28 00:00:00 │ └─────────────────────┘ (1 row)
Pokud budeme natvrdo počítat, že měsíc má 30 dní, tak uděláme následující chybu:
postgres=> SELECT '2019-01-31'::timestamp + interval '30 days'; ┌─────────────────────┐ │ ?column? │ ╞═════════════════════╡ │ 2019-03-02 00:00:00 │ └─────────────────────┘ (1 row)
Díky tomu, že interval je kompozitní typ a většinou se pracuje s jednotlivými položkami, pak se
například funkce EXTRACT chová zdánlivě divně (při pochopení implementace typu interval ale naprosto logicky):
postgres=> SELECT EXTRACT(days FROM interval '33 days'), EXTRACT(days FROM interval '1 month'); ┌───────────┬───────────┐ │ date_part │ date_part │ ╞═══════════╪═══════════╡ │ 33 │ 0 │ └───────────┴───────────┘ (1 row)
Můžeme si pomoci získáním hodnoty epoch, která si vynucuje přepočet na vteřiny, a při kterém se provádí
převod 1 měsíc = 30 dní:
postgres=> SELECT EXTRACT(epoch FROM interval '33 days'), EXTRACT(epoch FROM interval '1 month'); ┌───────────┬───────────┐ │ date_part │ date_part │ ╞═══════════╪═══════════╡ │ 2851200 │ 2592000 │ └───────────┴───────────┘ (1 row)
Rozpuštění dnů v měsíce (předpokládáme 30 denní měsíc) zajistí funkce justify_days. Převod hodin na dny
(předpokládá se, že den má 24 hodin) zajistí funkce justify_hours. Případně obě zmíněné transformace
provede funkce justify_interval:
postgres=> select justify_days('33 days 1000 hours');
┌─────────────────────────┐
│ justify_days │
╞═════════════════════════╡
│ 1 mon 3 days 1000:00:00 │
└─────────────────────────┘
(1 row)
postgres=> select justify_hours('33 days 1000 hours');
┌──────────────────┐
│ justify_hours │
╞══════════════════╡
│ 74 days 16:00:00 │
└──────────────────┘
(1 row)
postgres=> select justify_interval('33 days 1000 hours');
┌─────────────────────────┐
│ justify_interval │
╞═════════════════════════╡
│ 2 mons 14 days 16:00:00 │
└─────────────────────────┘
(1 row)
Rozdíl dvou timestampů je typu interval. Má význam počtu mikrosekund mezi definovanými okamžiky. Pro některé
výpočty (např. určení věku) tento způsob není praktický - protože z mikrosekund zpětně nedopočítáme, kolik
skutečných měsíců, roků je rozdíl.
postgres=> SELECT justify_interval(current_timestamp - '19730715 06:00:00'); ┌────────────────────────────────────────┐ │ justify_interval │ ╞════════════════════════════════════════╡ │ 46 years 3 mons 9 days 23:17:51.988105 │ └────────────────────────────────────────┘ (1 row)
Proto musíme použít funkci age, která počítá rozdíl jiným způsobem (tak jak bychom jej počítali ručně):
postgres=> SELECT age(current_timestamp, '19730715 06:00:00'); ┌────────────────────────────────────────┐ │ age │ ╞════════════════════════════════════════╡ │ 45 years 7 mons 8 days 23:18:19.549346 │ └────────────────────────────────────────┘ (1 row)
Všimněte si, že výsledky jsou dost rozdílné - v obou případech se jedná o rozdíl dvou intervalů. V každém příkladu se ovšem použil jiný algoritmus, a také jsme dostali jiný výsledek.
Typ interval podporuje ohromnou škálu různých zápisů (ať už z důvodů zachování kompatibility
s historickými verzemi Postgresu, ANSI SQL případně Oraclem):
| interval ‘1‘ | 1 sec |
| interval ‘:1‘ | 1 sec |
| interval ‘1sec‘ | 1 sec |
| interval ‘1‘ hour | 1 hodina |
| ‘1‘::interval hour | 1 hodina |
| ‘1 hour‘:: interval | 1 hodina |
| ‘1:‘::interval | 1 hodina |
Osobně bych doporučil používat vždy jednotky a vyhýbal bych se zápisům, které nejsou na první pohled zřejmé.
Vsuvka - hodnota neznámého typu
PostgreSQL patří mezi striktně typové systémy (s výjimkou která potvrzuje pravidlo - typ RECORD v PLpgSQL).
Typ hodnoty je dán definicí sloupečku v tabulce, případně specifikací typu parametru při volání SQL API.
Pro každou hodnotu je nutné znát její typ. Pokud typ neznáme, v SQL příkazu se vyskytuje číselná nebo
řetězcová konstanta, pak se typ odvodí na základě zápisu (čísla) nebo kontextu (řetězce).
| 1000 | celé číslo (typ int)
|
| 100.1 | desetiné číslo (typ numeric)
|
| 'AHOJ' | hodnota neznámého typu (typ unknown)
|
U hodnot neznámého typu se pro určení skutečného typu vychází z kontextu. Známe typy použité u operátorů,
známe typy parametrů funkcí. Např. parametrem funkce sin je hodnota typu double precision, takže mohu zapsat
SELECT sin('0.5');
a typový systém odvodí, že řetězec "0.5" může být pouze typu double precision, a automaticky se volá
input funkce pro tento typ.
Pokud přičítám k intervalu hodnotu, tak použiji operátor +. Pokud je na jedné straně timestamp, pak na straně
druhé může být pouze interval. Pro řetězec se opět automaticky použije input funkce pro typ interval:
postgres=> SELECT current_timestamp + '1 day'; ┌───────────────────────────────┐ │ ?column? │ ╞═══════════════════════════════╡ │ 2019-02-25 05:49:25.454819+01 │ └───────────────────────────────┘ (1 row)
Problémy nastávají, pokud systém nedokáže přiřadit typ jednoznačně (Postgres umožňuje přetížení jak operátorů tak funkcí). Postgres z kontextu získá seznam možných typů pro zadanou hodnotu (zatím neznámého typu). Pokud jsou tyto typy stejné <a href="https://www.postgresql.org/docs/11/catalog-pg-type.html">kategorie</a>, a pro tuto kategorii existuje tzv preferovaný typ, pak se jako typ hodnoty zvolí preferovaný typ. V opačném případě systém zahlásí chybu a vynutí si explicitně zadaný typ hodnoty.
Popsaný systém ušetří v řadě případů explicitní typování (což je dobře - kompatibilita, komfort uživatele).
Na druhou stranu je to určitá heuristika, která komplikuje chování systému. Všimněte si, že popsaný mechanismus
odvození typu nikde nepracuje s vlastní hodnotou. Typ se neodvozuje z formátu zápisu hodnoty, ale z kontextu
použití hodnoty. Což je jasné programátorům, ale dost špatně se vysvětluje běžným uživatelům. Ti chtějí zapsat
něco jako interval, a dostat hodnotu typu interval. Tak ale Postgres nefunguje.
Pro demonstraci si vytvořím sadu přetížených funkcí:
CREATE OR REPLACE FUNCTION public.foo(timestamp with time zone)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'foo - timestamp with time zone';
END;
$function$;
CREATE OR REPLACE FUNCTION public.foo(timestamp with time zone)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'foo - timestamp with time zone';
END;
$function$;
postgres=> SELECT foo('2019-02-24 6:16:00');
NOTICE: foo - timestamp with time zone
Funkci foo volám s parametrem, který vypadá jako timestamp bez časové zóny. Nicméně je to hodnota neznámého
typu. Z kontextu může být timestampem s časovou zónou i bez časové zóny. To znamená kolizi typů. "Naštěstí", oba typy
jsou stejné kategorie, a timestamp s časovou zónou je označený jako preferovaný, tudíž se v tomto případě zvolí.
Způsob zápisu nehraje roli. Pokud chci zavolat funkci pro timestamp bez časové zóny, tak musím explicitně typovat:
postgres=> SELECT foo('2019-02-24 6:16:00'::timestamp without time zone);
NOTICE: foo - timestamp without time zone
Pokud přidám další funkci foo s typem interval, tak už mne popsaná heuristika nezachrání:
CREATE OR REPLACE FUNCTION public.foo(interval)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'foo - interval';
END;
$function$;
postgres=> SELECT foo('1 day');
ERROR: function foo(unknown) is not unique
LINE 1: SELECT foo('1 day');
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.
Obsah je jednoznačně typu interval (pro detekci typu ale obsah není důležitý). Nyní musím vždy explicitně typovat:
postgres=> SELECT foo('1 day'::interval);
NOTICE: foo - interval
Pamatujte si. V textovém řetězci nezáleží na tom, jaká hodnotu je v řetězci (pro určení typu). Záleží na tom, kde je řetězec použitý. Případně zda-li obsahuje informaci o typu:
| 'AHOJ' | hodnota neznámého typu |
| date '20190224' | hodnota typu date |
| '20190224'::date | hodnota typu date (proprietární syntax) |
| CAST('20190224' AS date) | hodnota typu date (ANSI SQL syntax) |
Generující funkce
Při zadávání hodnot výše zmíněných typů většinou v aplikaci sestavíme řetězec pro daný formát a vynutíme si přetypování nebo
zavoláme konverzní funkci. U přetypování je třeba dávat pozor na potenciálně nejednoznačné formáty zápisu: MDY, DMY a pokud
to je možné, tak je nepoužívat.
U konverzních funkcí (to_date, to_timestamp) vždy určujeme formát - čímž odpadá riziko nejednoznačného zápisu:
postgres=> SELECT to_date('24-02-2019', 'DD-MM-YYYY');
┌────────────┐
│ to_date │
╞════════════╡
│ 2019-02-24 │
└────────────┘
(1 row)
Pokud nepoužijeme ISO formát zápisu, tak se striktně doporučuje používat konverzní funkce.
Relativně nové jsou v Postgresu funkce make_date, make_interval, make_timestamp, make_timestamptz.
Pokud máte položky datumu v číselné podobě, tak můžete použít tyto funkce a nemusíte sestavovat řetězec:
postgres=> SELECT make_date(2019, 2, 24); ┌────────────┐ │ make_date │ ╞════════════╡ │ 2019-02-24 │ └────────────┘ (1 row) -- použití jmenné konvence předávání parametrů postgres=> SELECT make_interval(weeks => 2); ┌───────────────┐ │ make_interval │ ╞═══════════════╡ │ 14 days │ └───────────────┘ (1 row)
Zajímavé funkce
Určitě je dobré znát minimálně dvě funkce - date_part a date_trunc. První z jmenovaných funkcí
má ještě alternativní zápis jako funkce EXTRACT.
postgres=> SELECT EXTRACT(DOW FROM current_date), EXTRACT(YEAR FROM current_date); ┌───────────┬───────────┐ │ date_part │ date_part │ ╞═══════════╪═══════════╡ │ 0 │ 2019 │ └───────────┴───────────┘ (1 row)
date_trunc ořezává zadanou datumovou hodnotu na roky, měsíce, dny, hodiny, ...
postgres=> SELECT current_timestamp,
date_trunc('hour', current_timestamp),
date_trunc('week', current_timestamp);
┌───────────────────────────────┬────────────────────────┬────────────────────────┐
│ current_timestamp │ date_trunc │ date_trunc │
╞═══════════════════════════════╪════════════════════════╪════════════════════════╡
│ 2019-02-24 07:26:43.314709+01 │ 2019-02-24 07:00:00+01 │ 2019-02-18 00:00:00+01 │
└───────────────────────────────┴────────────────────────┴────────────────────────┘
(1 row)
Je dobré vědět, že existuje operátor OVERLAP pro detekci překryvu dvou intervalů.
Práce s časovými zónami, změna času
Časová zóna, časové pásmo je část země, která používá stejný standardní čas. Základním časovým pásmem je pásmo ve kterém platí koordinovaný světový čas (UTC). Toto pásmo se rozkládá kolem nultého poledníku, který prochází Královskou observatoří v Greenwichi. Z tohoto důvodu se tomuto pásmovému času také říká GMT. Ostatní časová pásma jsou popsána rozdílem počtu hodin, o kolik se v nich liší čas od UTC. Např. středoevropský čas CET (česky SEČ) je označen jako UTC+1 (čas je oproti UTC posunutý o hodinu napřed).
Pokud pracujeme s časem, můžeme pracovat s lokálním časem nebo časem specifikovaným vůči konkrétní časové zóně - s použitím operátoru "AT TIME ZONE" nebo s přenastavením konfigurační proměnné timezone:
postgres=> SHOW timezone; ┌───────────────┐ │ TimeZone │ ╞═══════════════╡ │ Europe/Prague │ └───────────────┘ (1 row)
Časová zóna se v hodnotách typu timestamp with time zone zobrazuje vždy jako interval vůči UTC. A jelikož
u typu timestamp with time zone se hodnoty zobrazují v lokální časové zóně (minulé, stávající nebo budoucí),
tak vždy vidíme hodnoty vztahující se časové zóně klienta.
Časovou zónu můžeme určit názvem. Seznam dostupných názvů získáme dotazem:
postgres=> SELECT * FROM pg_timezone_names; ┌──────────────────────┬────────┬────────────┬────────┐ │ name │ abbrev │ utc_ofset │ is_dst │ ╞══════════════════════╪════════╪════════════╪════════╡ │ Africa/Addis_Ababa │ EAT │ 03:00:00 │ f │ │ Africa/Dar_es_Salaam │ EAT │ 03:00:00 │ f │ │ Africa/Freetown │ GMT │ 00:00:00 │ f │ │ Africa/Gaborone │ CAT │ 02:00:00 │ f │ │ Africa/Algiers │ CET │ 01:00:00 │ f │ │ Africa/Brazzaville │ WAT │ 01:00:00 │ f │ │ Africa/Dakar │ GMT │ 00:00:00 │ f │ │ Africa/Maputo │ CAT │ 02:00:00 │ f │ │ Africa/Asmara │ EAT │ 03:00:00 │ f │
Časovou zónu můžeme také určit zkratkou. Jejich seznam získáme dotazem:
postgres=> SELECT * FROM pg_timezone_abbrevs; ┌────────┬────────────┬────────┐ │ abbrev │ utc_ofset │ is_dst │ ╞════════╪════════════╪════════╡ │ ACDT │ 10:30:00 │ t │ │ ACSST │ 10:30:00 │ t │ │ ACST │ 09:30:00 │ f │ │ ACT │ -05:00:00 │ f │ │ ACWST │ 08:45:00 │ f │ │ ADT │ -03:00:00 │ t │ │ AEDT │ 11:00:00 │ t │ │ AESST │ 11:00:00 │ t │ │ AEST │ 10:00:00 │ f │
Pro Prahu:
postgres=> SELECT * FROM pg_timezone_names WHERE name ~* 'Prague'; ┌───────────────┬────────┬────────────┬────────┐ │ name │ abbrev │ utc_ofset │ is_dst │ ╞═══════════════╪════════╪════════════╪════════╡ │ Europe/Prague │ CET │ 01:00:00 │ f │ └───────────────┴────────┴────────────┴────────┘ (1 row)
Vůči UTC (Londýnu) máme o 1 hodinu více.
Časovou zónu můžeme zadat jako interval vůči UTC.
Konečně časovou zónu můžeme zadat skrze tzv POSIX time zone offset. To je fakt dobrá past. Zápis je podobný jako v případě obecného zápisu časové zóny intervalem (např. UTC-7), nicméně znaménko se interpretuje opačně. Například časová zóna "UTC-7" - středozápad US se v POSIXové notaci zapisuje jako "UTC+7".
Postgres je UNIXová databáze, takže POSIXová notace zápisu časové zóny je samozřejmě podporována, a do jisté míry prioritizovaná (je tam sem tam nějaká výjimka, aby se to dalo používat i mimo US, a aby v tom byl trochu větší zmatek).
Pokud se časová zóna předává jako řetězec neznámého typu, pak interní parser zkouší:
- detekovat název časové zóny
- detekovat zkrácený název časové zóny
- detekovat POSIX ofset časové zóny
- detekovat interval
Pozor na některých místech se o interpretaci intervalu nepokouší, a hodnota se interpretuje jako POSIX ofset
postgres=> SELECT '2019-02-24 10:00:00 CET'::timestamp with time zone; ┌────────────────────────┐ │ timestamptz │ ╞════════════════════════╡ │ 2019-02-24 10:00:00+01 │ └────────────────────────┘ (1 row) postgres=> SELECT '2019-02-24 10:00:00 +1'::timestamp with time zone; ┌────────────────────────┐ │ timestamptz │ ╞════════════════════════╡ │ 2019-02-24 10:00:00+01 │ └────────────────────────┘ (1 row) -- pozor POSIX ofset postgres=> SELECT '2019-02-24 10:00:00 UTC+1'::timestamp with time zone; ┌────────────────────────┐ │ timestamptz │ ╞════════════════════════╡ │ 2019-02-24 12:00:00+01 │ └────────────────────────┘ (1 row)
Linux roky používám, a o POSIXové notaci zápisu časové zóny jsem netušil. Když jsem se o tom dočetl v dokumentaci Postgresu, tak jsem nevěřil vlastním očím. Pak při troše googlování člověk zjistí, že to je obecný problém, a je to daň za kompatibilitu s API navrženým před 40 roky. Pozor na to. Pokud se člověk vyhne nešťastnému formátu a časovou zónu bude specifikovat zkratkou nebo názvem, tak by neměl mít problém.
Pokud se bavíme o změně letního/zimního času, je nutné si uvědomit, že čas se nemění, a že si pořád
pokračuje svým tempem. To co se mění, je aktuální časová zóna. Její změnou dochází ke změně zobrazení (vizualizaci)
času. Jak operační systém, tak Postgres (pro typ timestamp with time zone) používá UTC. Tudíž změnu času
vlastně vůbec řešit nemusíme - interně se vůbec nic nemění. Mění se jen zobrazení.
-- zobrazení hodnoty v zimním čase pro Europe/Prague postgres=> SELECT '2019-02-24 10:00:00'::timestamp with time zone; ┌────────────────────────┐ │ timestamptz │ ╞════════════════════════╡ │ 2019-02-24 10:00:00+01 │ └────────────────────────┘ (1 row) -- zobrazení hodnoty v letním čase pro Europe/Prague postgres=> SELECT '2019-05-24 10:00:00'::timestamp with time zone; ┌────────────────────────┐ │ timestamptz │ ╞════════════════════════╡ │ 2019-05-24 10:00:00+02 │ └────────────────────────┘ (1 row)
Postgres má interní databázi platností časových zón pro každou lokalitu, z které lze odvodit, že v květnu už bude letní čas (UTC+2).
Operátor AT TIME ZONE
Tento operátor slouží k převodu timestampu bez časové zóny (without time zone) do timestampu s časovou
zónou (with time zone) a naopak. TimestampTz (with time zone) se vždy zobrazuje v lokálním
času (časové zóně), tudíž konverze Timestamp ⭢ TimestampTz je mapování času jiné zóny na lokální
(klientskou) časovou zónu. Konverze TimestampTz ⭢ Timestamp mapuje UTC (lokální čas) na jinou časovou
zónu:
-- timestamptz -> timestamp postgres=> SELECT '2019-02-24 12:00:00'::timestamp with time zone AT TIME ZONE 'America/New_York'; ┌─────────────────────┐ │ timezone │ ╞═════════════════════╡ │ 2019-02-24 06:00:00 │ └─────────────────────┘ (1 row)
12:00:00 středoevropského času (lokálního) je 06:00:00 v New Yorku.
Naopak
-- timestamp -> timestamptz postgres=> SELECT '2019-02-24 12:00:00'::timestamp without time zone AT TIME ZONE 'America/New_York'; ┌────────────────────────┐ │ timezone │ ╞════════════════════════╡ │ 2019-02-24 18:00:00+01 │ └────────────────────────┘ (1 row)
Ve 12:00:00 v New Yorku je 18:00:00 lokálního času (Europe/Prague).
Schůze v 8:00:00 v New Yorku začne:
postgres=> SELECT '2019-02-25 8:00:00'::timestamp without time zone AT TIME ZONE 'America/New_York'; ┌────────────────────────┐ │ timezone │ ╞════════════════════════╡ │ 2019-02-25 14:00:00+01 │ └────────────────────────┘ (1 row)
ve 14:00:00 v Praze, a
postgres=> SELECT ('2019-02-25 8:00:00'::timestamp without time zone AT TIME ZONE 'America/New_York') AT TIME ZONE 'Europe/Moscow';
┌─────────────────────┐
│ timezone │
╞═════════════════════╡
│ 2019-02-25 16:00:00 │
└─────────────────────┘
(1 row)
v 16:00 v Moskvě.
Na co si dát pozor
První a asi největší problém je zavedení POSIXového ofsetu pro značení časových zón, které se formátem podobá definici časové zóny intervalem (obě definice mají průnik formátů), ale má opačné znaménko. U POSIXového ofsetu jde kladná osa na západ od Londýna. U intervalu naopak na východ.
-- použití Posix ofsetu, případně "UTC+6" postgres=> SELECT current_timestamp AT TIME ZONE '+1:0'; ┌────────────────────────────┐ │ timezone │ ╞════════════════════════════╡ │ 2019-02-24 06:08:18.740261 │ └────────────────────────────┘ (1 row) -- použití intervalu postgres=> SELECT current_timestamp AT TIME ZONE '+1:0'::interval; ┌────────────────────────────┐ │ timezone │ ╞════════════════════════════╡ │ 2019-02-24 08:08:24.277149 │ └────────────────────────────┘ (1 row)
Předpokládám, že většina uživatelů (bez důkladnějšího prostudování dokumentace) netuší, že něco jako POSIX ofset existuje a bude se divit, proč dostávají o 2 hodiny posunuté výsledky (aktuálně je časová zóna +1). Člověk by si mohl myslet, že definuje časovou zónu intervalem, ale bez explicitní specifikace typu je to jen dojem. Pamatujte si, že POSIX standard je US centristický. Z toho pak plynou opačná znaménka. Pokud chcete předejít zmatkům, nepoužívejte pro určení časové zóny ofset nebo interval, ale pokud to lze, použijte její název nebo zkratku (např. CET nebo Europe/Prague).
Podobný problém je i na levé straně operátoru AT TIME ZONE. Operátor AT TIME ZONE převádí hodnotu z typu
timestamp with time zone na timestamp without time zone a naopak. Pokud zadám hodnotu jako timestamp bez
časové zóny (timestamp without time zone), tak výsledkem by měl být timestamp s časovou zónou. Jak vidíte,
není:
postgres=> SELECT '2019-02-24 8:16:00' AT TIME ZONE 'UTC'; ┌─────────────────────┐ │ timezone │ ╞═════════════════════╡ │ 2019-02-24 07:16:00 │ └─────────────────────┘ (1 row)
Proč? Opět nezáleží na způsobu zápisu, ale na kontextu. A z kontextu vyplývá, že na dané pozici může být
jak timestamp with time zone, tak timestamp without time zone. timestamp with time zone je preferovaný typ,
a tudíž se použije pro hodnoty bez známého typu (viz vsuvka). Výsledkem je tedy správně timestamp without
time zone.
Pokud chci na levé straně použít timestamp without time zone, tak jej musím explicitně specifikovat:
postgres=> SELECT '2019-02-24 8:16:00'::timestamp without time zone AT TIME ZONE 'UTC'; ┌────────────────────────┐ │ timezone │ ╞════════════════════════╡ │ 2019-02-24 09:16:00+01 │ └────────────────────────┘ (1 row)
Žádná jiná zrada tam není - i když uznávám, že popsané chování může být pro uživatele bez znalostí typového systému Postgresu dost těžko stravitelné. Na druhou stranu, když pochopím, jak Postgres funguje, zejména co se týká hodnot bez neznámého typu, tak je vše jasné a konzistentní.
Závěr
Musím uznat, že při práce s typy pro datum a čas nemusí být vždy úplně intuitivní. Na druhou stranu, uživatel může narazit asi pouze na dvě situace, kdy se bude muset podívat do dokumentace. Mně osobně se vždy s časem lépe pracovalo v Postgresu než v jiných prostředích, které jsem používal.