Práce s časem v Oracle databázi

Ve třetí části série článků o relačních databázích si ukážeme základy práce s časem.
 Databáze poskytuje mnoho funkcí pro práci s časem, v této kapitole se však budeme zabývat jen tím, jak čas a datum získat. Protože problémy způsobují časové zóny, rovněž se zmíním i o této problematice.

V Oracle databázi máme dva časové zdroje: systémový čas a databázový čas.

Sytémový čas

Máme dotaz:

select sysdate, systimestamp, SYS_EXTRACT_UTC(SYSTIMESTAMP) utc from dual;

Výše uvedený dotaz vrací čas operačního systému:

SYSDATE             SYSTIMESTAMP                        UTC                       
------------------- ----------------------------------- ----------------------------
03.03.15 13:26:41   03.03.15 13:26:41,365645000 +01:00  03.03.15 12:26:41,365645000 


Rozdíl mezi sysdate a systimestamp není jen v přesnosti. Důležité je, že sysdate neobsahuje informaci o časové zóně. V okamžiku volání sysdate se aktuální zóna "aplikuje" do hodnoty a ta se pak vrátí v dotazu. Takto zýskaný a ukládány čas může způsobit obrovské problémy:

Příklad
Představte si, že Vaše aplikace vkládá do sloupečku hodnotu SYSDATE pro zaznamání vzniku řádku a db server běží v Praze. Pokud db server přesune do Austrálie - nově vzniklé hodnoty budou ukládány s aplikovaným časovým posunem +11 ale staré hodnoty byly uloženy např. s posunem +2. Pokud tedy vznikly dva řádky v rozmezí 1 minuty ale v jiných zónách (+11 a +2), výpočty nad těmito časy (např. doba mezi vložením dvou záznamů) nebudou odpovídat realitě.

Jak tedy správně ukládat čas (a datum) v databázi? Existují 2 způsoby:

• Záznam bude uložen včetně časové zóny a pro zjištění se užije SYSTIMESTAMP
• Záznam bude uložen v UTC (bez časového posunu, +0h).

Právě druhý zminovaný způsob je šetrný na diskový prostor a často i na výkon. K získání času v UTC je ukázáno v třetím sloupečku předchozího příkladu, tedy SYS_EXTRACT_UTC(SYSTIMESTAMP). Nyní je jedno, ve které časové zóně se server nachází, všechny časy budou v UTC.

Pro práci s časem se může hodit časová zóna operačního systému:

select to_char(SYSTIMESTAMP,'TZR') timezone from dual;

Výslekek je pak například

 TIMEZONE                     
--------------------------------
+01:00     

Databázový čas 

S databázovým časem  se pracuje podobně jako se systémovým časem. Hlavní rozdíl je v možnosti nastavení časových zón platných v rámci databáze (tedy ne pro celý operační systém) nebo pro jedno sezení (session).

Analogický dotaz k systémovému času

select CURRENT_DATE,CURRENT_TIMESTAMP,SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) utc from dual;

Výsledek je pro tentokrát zcela totožný se systémovým časem:

 CURRENT_DATE        CURRENT_TIMESTAMP                   UTC                      
------------------- ----------------------------------- ----------------------------
03.03.15 14:10:41   03.03.15 14:10:41,411721000 +01:00  03.03.15 13:10:41,411721000  

Stejne tak zjištění časové zony databáze (aktuálního sezení)

select SESSIONTIMEZONE timezone from dual;

Oproti systémovému času si můžeme nyní časovou zónu upravit podle potřeby:

ALTER SESSION SET TIME_ZONE = '+11:0'; 

Nastavíme si časový posun +11 hod a okamžitě zkusíme opět dotaz k získání času databáze. Můžeme porovnat výsledky:

CURRENT_DATE        CURRENT_TIMESTAMP                   UTC                       
------------------- ----------------------------------- ----------------------------
04.03.15 00:15:23   04.03.15 00:15:23,064275000 +11:00  03.03.15 13:15:23,064275000  

Zatímco UTC čas se nezměnil, hodnota CURRENT_DATE a CURRENT_TIMESTAMP se změnila. U hodnoty CURRENT_TIMESTAMP nám to ovšem nevadí, časová zóna je je součástí hodnoty. Zato však CURRENT_DATE nám bude vracet jiné hodnoty, které (vysvětleno výše na příkladu) nesmí být smýchány s hodnotami před změnou časové zóny.

Závěr

V databázi existuje pochopitelně nepřeberné množství funkcí pro práci s časem. Účelem tohoto článku bylo jen naťuknout tuto problematiku a poukázat na nejpalčivější problém týkající se časových zón. Nyní je již vše na samotném čtenáři, jak se tohoto tématu chopí:) 
Času zdar.

Tabulka dual

Z mých zkušeností zná tuto tabulku každý databázový vývojář a v podstatě žádný Java/ORM vývojář, což není překvapující - pokud programujete přímo v databázi, některé konstrukce nejsou bez této tabulky zbytečně obtížné, ne-li nemožné. Pro programátora užívající ORM pak "ztácí" tabulka význam. Slovo "ztrácí" je v uvozovkách oprávněně: Na běžnou práci v ORM skutečně tabulka není potřeba. Pokud chceme databázi používat efektivně a nebo jsme nuceni volat některé databázové funkce přímo, tabulka se nám náramě hodí. 

Tabulka dual je tabulka vlastní všem Oracle databázím a obsahuje typicky  jeden sloupec jménem DUMMY a jeden řádek s hodnotou 'X' typu VARCHAR2(1).
select * from dual;

Proč je tedy tak důležitá? Pomocí této tabulky lze libovolnou hodnotu, literál či výstup funkce převést do relace - t.j. tabulky.

na příklad:

select 'hello world' from dual;
select 1+1 from dual;
select sysdate from dual;

Všechny tyto příklady mají jednu věc společnou - vytvářejí z "ničeho" tabulku. Velmi často se tabulka hodí pro volání databázových funkcí. 

select 1+9/4, trunc(1+9/4),DBMS_RANDOM.RANDOM from dual;

Dotaz ukazuje volání funkce "trunc", jež slouží oříznutí desetinné části z čísla a zároveň volá funkci pro generování náhodného čísla.

Výsledek dotazů je pak třísloupcová tabulka:

1+9/4 TRUNC(1+9/4) RANDOM
----- ------------ ------
 3,25            3 610212023

Z předchozích příkladů je patrný význam tabulky dual: slouží nám jako jakýsi můstek mezi konvenčním procedurálním a relačním přístupem. Článek ukázal nejzákladnější použití tabulky. V dalších dílech bude tabulka využita v mnohem větší míře a její účel bude pak jasnější. Tak zase příště.