Datové typy pro práci s datumem a časem v PostgreSQL

Z PostgreSQL
Skočit na navigaci Skočit na vyhledávání

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ší:

  1. detekovat název časové zóny
  2. detekovat zkrácený název časové zóny
  3. detekovat POSIX ofset časové zóny
  4. 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 TimestampTimestampTz je mapování času jiné zóny na lokální (klientskou) časovou zónu. Konverze TimestampTzTimestamp 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.