Zobrazení zdroje stránky Datové typy pro práci s datumem a časem v PostgreSQL


[[Category:Články]]
Autor: ''Pavel Stěhule''

Před vánocemi mne jeden ze čtenářů požádal o doplnění mého [http://postgres.cz/files/tahak_postgresql-10.pdf 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.

{|class="wikitable"
|-
|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: <code>date</code>, <code>time</code>, <code>timestamp</code> a <code>interval</code>. 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 <code>date</code> je celé číslo (počet dnů). Rozdíl dvou hodnot typu <code>timestamp</code> je hodnota typu <code>interval</code>.
Pro jednoduché operace, kde se pracuje se dny, se používá typ <code>date</code> a jednoduchá celočíselná aritmetika. Pro ostatní
operace je tu <code>timestamp</code> a <code>interval</code>. Zde žádné temné zákoutí nevidím, a tento model mi odjakživa vyhovoval (a celočíselný
typ <code>date</code> 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 <code>to_char</code>, <code>to_date</code>). 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 <code>date</code> používáme pro uložení dne - např. 21.2.2019. Interně je to <code>integer</code> (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 <i>n</i> se posouváme v čase vpřed o <i>n</i> dnů, naopak odečtením <i>n</i> se posouváme v čase zpět.
<pre>
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)
</pre>
Aktuální den vrací (pseudo) funkce <code>current_date</code>.

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 <i>ISO formát</i>
<code>YYYY-MM-DD</code> (případně <code>YYYYMMDD</code>). Tento formát je jednoznačný. Naopak zde používaný (v PostgreSQL označovaný jako <i>German</i> nebo <code>DMY</code>)
formát <code>DD-MM-YYYY</code> (den-měsíc-rok) by se používat neměl, protože zde hrozí záměna z formátem <code>MDY</code> (měsíc-den-rok). To, jak Postgres
interpretuje hodnoty závisí na nastavení konfigurační proměnné <code>datestyle</code>. 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.

{|class="wikitable"
|-
|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 <code>infinity</code> a <code>-infinity</code>.

==Time==

Pro uložení času bez informace o dni je tu typ <code>time</code>. Jedná se o 8 bajtovou hodnotu (fakticky <code>bigint</code>), kde se čas zakóduje
jako počet mikrosekund od počátku, v tomto případě 0:0:0. Typ <code>time</code> 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 <code>time</code> může být buďto s oddělovačem <code>':'</code> nebo bez oddělovače:

{|class="wikitable"
|-
|12:00:00.0000
|poledne
|-
|120000.0000
|poledne
|}

==Timestamp==

Zkombinováním typu <code>date</code> a <code>time</code> vznikne typ <code>timestamp</code>. V něm jsme schopni specifikovat určitý čas určitého dne. V Postgresu
typ <code>timestamp</code> 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:
<code>timestamp</code> pro <code>timestamp without time zone</code> a <code>timestamptz</code> pro <code>timestamp
with time zone</code>.

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 [https://cs.wikipedia.org/wiki/Koordinovan%C3%BD_sv%C4%9Btov%C3%BD_%C4%8Das ''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á <code>timezone</code>). 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í.
<pre>
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)
</pre>

Výběr intervalu časové zóny se neřídí aktuálním dnem, ale dnem ukládané nebo zobrazené hodnoty. Pro typ
<code>timestamp with time zone</code> 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 <code>timestamp with time zone</code>. 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 <code>timestamp without time zone</code>.

U typu <code>timestamp without time zone</code> 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 <code>timestamp without time zone</code> používat v tabulkách. Tento typ má svůj význam a použití, ale nepoužívejte
jej v příkazech <code>CREATE TABLE</code>, <code>ALTER TABLE</code>.

Také typ <code>timestamp</code> podporuje hodnoty <code>infinity</code> nebo <code>-infinity</code>.

Docela častou otázkou je zobrazení hodnoty typu <code>timestamp</code> pouze na sekundy. U tohoto typu můžeme definovat
přesnost - <code>timestamp(0)</code> je čas pouze v sekundách, <code>timestamp(6)</code> je výchozí formát - čas v mikrosekundách:
<pre>
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)
</pre>
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 <code>timestamp</code> přičítá, odčítá typ <code>interval</code>. Tento typ je jedním z nejkomplexnějších vestavěných typů
v PostgreSQL. <i>Interně se jedná o strukturu se třemi položkami</i> - pro měsíce, dny a sekundy. Pro pochopení
chování typu <code>interval</code> 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.
<pre>
postgres=> SELECT interval '300 days 48 hours';
┌───────────────────┐
│     interval      │
╞═══════════════════╡
│ 300 days 48:00:00 │
└───────────────────┘
(1 row)
</pre>
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í:
<pre>
postgres=> SELECT interval '12 month 1 year 3600 sec 60 min';
┌──────────────────┐
│     interval     │
╞══════════════════╡
│ 2 years 02:00:00 │
└──────────────────┘
(1 row)
</pre>
Případně odečítají:
<pre>
postgres=> SELECT interval '1 month -1 day -1 hour +7200 sec';
┌─────────────────────────┐
│        interval         │
╞═════════════════════════╡
│ 1 mon -1 days +01:00:00 │
└─────────────────────────┘
(1 row)
</pre>
Zajímavě (a prakticky) je implementováno přičítání měsíců (<code>interval</code>) 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:
<pre>
postgres=> SELECT '2019-01-31'::timestamp + interval '1month';
┌─────────────────────┐
│      ?column?       │
╞═════════════════════╡
│ 2019-02-28 00:00:00 │
└─────────────────────┘
(1 row)
</pre>
Pokud budeme natvrdo počítat, že měsíc má 30 dní, tak uděláme následující chybu:
<pre>
postgres=> SELECT '2019-01-31'::timestamp + interval '30 days';
┌─────────────────────┐
│      ?column?       │
╞═════════════════════╡
│ 2019-03-02 00:00:00 │
└─────────────────────┘
(1 row)
</pre>
Díky tomu, že interval je kompozitní typ a většinou se pracuje s jednotlivými položkami, pak se
například funkce <code>EXTRACT</code> chová zdánlivě divně (při pochopení implementace typu <code>interval</code> ale naprosto logicky):
<pre>
postgres=> SELECT EXTRACT(days FROM interval '33 days'), EXTRACT(days FROM interval '1 month');
┌───────────┬───────────┐
│ date_part │ date_part │
╞═══════════╪═══════════╡
│        33 │         0 │
└───────────┴───────────┘
(1 row)
</pre>
Můžeme si pomoci získáním hodnoty <code>epoch</code>, 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í:
<pre>
postgres=> SELECT EXTRACT(epoch FROM interval '33 days'), EXTRACT(epoch FROM interval '1 month');
┌───────────┬───────────┐
│ date_part │ date_part │
╞═══════════╪═══════════╡
│   2851200 │   2592000 │
└───────────┴───────────┘
(1 row)
</pre>
Rozpuštění dnů v měsíce (předpokládáme 30 denní měsíc) zajistí funkce <code>justify_days</code>. Převod hodin na dny
(předpokládá se, že den má 24 hodin) zajistí funkce <code>justify_hours</code>. Případně obě zmíněné transformace
provede funkce <code>justify_interval</code>:
<pre>
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)
</pre>
Rozdíl dvou timestampů je typu <code>interval</code>. 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.
<pre>
postgres=> SELECT justify_interval(current_timestamp - '19730715 06:00:00');
┌────────────────────────────────────────┐
│            justify_interval            │
╞════════════════════════════════════════╡
│ 46 years 3 mons 9 days 23:17:51.988105 │
└────────────────────────────────────────┘
(1 row)
</pre>
Proto musíme použít funkci <code>age</code>, která počítá rozdíl jiným způsobem (tak jak bychom jej počítali ručně):
<pre>
postgres=> SELECT age(current_timestamp, '19730715 06:00:00');
┌────────────────────────────────────────┐
│                  age                   │
╞════════════════════════════════════════╡
│ 45 years 7 mons 8 days 23:18:19.549346 │
└────────────────────────────────────────┘
(1 row)
</pre>
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 <code>interval</code> 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):

{|class="wikitable"
|-
|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 <code>RECORD</code> v <i>PLpgSQL</i>).
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).

{|class="wikitable"
|-
|1000
|celé číslo (typ <code>int</code>)
|-
|100.1
|desetiné číslo (typ <code>numeric</code>)
|-
|'AHOJ'
|hodnota neznámého typu (typ <code>unknown</code>)
|}

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 <code>sin</code> je hodnota typu <code>double precision</code>, takže mohu zapsat
<pre>
SELECT sin('0.5'); 
</pre>
a typový systém odvodí, že řetězec "0.5" může být pouze typu <code>double precision</code>, a automaticky se volá
<i>input funkce</i> pro tento typ.

Pokud přičítám k intervalu hodnotu, tak použiji operátor <code>+</code>. Pokud je na jedné straně <code>timestamp</code>, pak na straně
druhé může být pouze <code>interval</code>. Pro řetězec se opět automaticky použije input funkce pro typ <code>interval</code>:
<pre>
postgres=> SELECT current_timestamp + '1 day';
┌───────────────────────────────┐
│           ?column?            │
╞═══════════════════════════════╡
│ 2019-02-25 05:49:25.454819+01 │
└───────────────────────────────┘
(1 row)
</pre>
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 <i>preferovaný typ</i>, 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 <code>interval</code>, a dostat hodnotu typu <code>interval</code>. Tak ale Postgres nefunguje.

Pro demonstraci si vytvořím sadu přetížených funkcí:
<pre>
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
</pre>
Funkci <code>foo</code> 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:
<pre>
postgres=> SELECT foo('2019-02-24 6:16:00'::timestamp without time zone);
NOTICE:  foo - timestamp without time zone
</pre>
Pokud přidám další funkci <code>foo</code> s typem <code>interval</code>, tak už mne popsaná heuristika nezachrání:
<pre>
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.
</pre>
Obsah je jednoznačně typu interval (pro detekci typu ale obsah není důležitý). Nyní musím vždy explicitně
typovat:
<pre>
postgres=> SELECT foo('1 day'::interval);
NOTICE:  foo - interval
</pre>
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:

{|class="wikitable"
|-
|'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: <code>MDY</code>, <code>DMY</code> a pokud
to je možné, tak je nepoužívat.

U konverzních funkcí (<code>to_date</code>, <code>to_timestamp</code>) vždy určujeme formát - čímž odpadá riziko nejednoznačného zápisu:
<pre>
postgres=> SELECT to_date('24-02-2019', 'DD-MM-YYYY');
┌────────────┐
│  to_date   │
╞════════════╡
│ 2019-02-24 │
└────────────┘
(1 row)
</pre>
Pokud nepoužijeme ISO formát zápisu, tak se striktně doporučuje používat konverzní funkce.

Relativně nové jsou v Postgresu funkce <code>make_date</code>, <code>make_interval</code>, <code>make_timestamp</code>, <code>make_timestamptz</code>.

Pokud máte položky datumu v číselné podobě, tak můžete použít tyto funkce a nemusíte sestavovat
řetězec:
<pre>
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)
</pre>
==Zajímavé funkce==

Určitě je dobré znát minimálně dvě funkce - <code>date_part</code> a <code>date_trunc</code>. První z jmenovaných funkcí
má ještě alternativní zápis jako funkce <code>EXTRACT</code>.
<pre>
postgres=> SELECT EXTRACT(DOW FROM current_date), EXTRACT(YEAR FROM current_date);
┌───────────┬───────────┐
│ date_part │ date_part │
╞═══════════╪═══════════╡
│         0 │      2019 │
└───────────┴───────────┘
(1 row)
</pre>
<code>date_trunc</code> ořezává zadanou datumovou hodnotu na roky, měsíce, dny, hodiny, ...
<pre>
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)
</pre>
Je dobré vědět, že existuje operátor <code>OVERLAP</code> pro detekci překryvu dvou intervalů.

==Práce s časovými zónami, změna času==

[[Wikipedia:Time zone|Č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:
<pre>
postgres=> SHOW timezone;
┌───────────────┐
│   TimeZone    │
╞═══════════════╡
│ Europe/Prague │
└───────────────┘
(1 row)
</pre>
Časová zóna se v hodnotách typu <code>timestamp with time zone</code> zobrazuje vždy jako interval vůči UTC. A jelikož
u typu <code>timestamp with time zone</code> 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:
<pre>
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      │
</pre>
Časovou zónu můžeme také určit zkratkou. Jejich seznam získáme dotazem:
<pre>
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      │
</pre>
Pro Prahu:
<pre>
postgres=> SELECT * FROM pg_timezone_names WHERE name ~* 'Prague';
┌───────────────┬────────┬────────────┬────────┐
│     name      │ abbrev │ utc_ofset  │ is_dst │
╞═══════════════╪════════╪════════════╪════════╡
│ Europe/Prague │ CET    │ 01:00:00   │ f      │
└───────────────┴────────┴────────────┴────────┘
(1 row)
</pre>
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), <b>nicméně znaménko se interpretuje
opačně</b>. 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ší:
<ol>
<li>detekovat název časové zóny
<li>detekovat zkrácený název časové zóny
<li>detekovat POSIX ofset časové zóny
<li>detekovat interval
</ol>
Pozor na některých místech se o interpretaci intervalu nepokouší, a hodnota se interpretuje jako POSIX ofset
<pre>
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)
</pre>
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 <code>timestamp with time zone</code>) 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í.
<pre>
-- 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)
</pre>
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 <code>AT TIME ZONE</code>==

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. <code>TimestampTz</code> (with time zone) se vždy zobrazuje v lokálním 
času (časové zóně), tudíž konverze <code>Timestamp</code> ⭢ <code>TimestampTz</code> je mapování času jiné zóny na lokální
(klientskou) časovou zónu. Konverze <code>TimestampTz</code> ⭢ <code>Timestamp</code> mapuje UTC (lokální čas) na jinou časovou
zónu:
<pre>
-- 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)
</pre>
12:00:00 středoevropského času (lokálního) je 06:00:00 v New Yorku.

Naopak
<pre>
-- 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)
</pre>
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:
<pre>
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)
</pre>
ve 14:00:00 v Praze, a 
<pre>
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)
</pre>
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. 
<pre>
-- 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)
</pre>
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 <code>AT TIME ZONE</code>. Operátor <code>AT TIME ZONE</code> převádí hodnotu z typu
<code>timestamp with time zone</code> na <code>timestamp without time zone</code> a naopak. Pokud zadám hodnotu jako timestamp bez
časové zóny (<code>timestamp without time zone</code>), tak výsledkem by měl být timestamp s časovou zónou. Jak vidíte,
není:
<pre>
postgres=> SELECT '2019-02-24 8:16:00' AT TIME ZONE 'UTC';
┌─────────────────────┐
│      timezone       │
╞═════════════════════╡
│ 2019-02-24 07:16:00 │
└─────────────────────┘
(1 row)
</pre>
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 <code>timestamp with time zone</code>, tak <code>timestamp without time zone</code>. <code>timestamp with time zone</code> je preferovaný typ,
a tudíž se použije pro hodnoty bez známého typu (viz vsuvka). Výsledkem je tedy správně <code>timestamp without
time zone</code>.

Pokud chci na levé straně použít timestamp without time zone, tak jej musím explicitně specifikovat:
<pre>
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)
</pre>
Žá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.