Datum a čas

• O datu a čase
• Datové typy
  • Konfigurace
  • Použití datových typů
  • Speciální hodnoty
• Formátování a konverze
• Funkce a operátory
• MySQL
  • Datové typy
  • Konfigurace
  • TIMESTAMP
  • Funkce
• SQLite
• Oracle
  • Datové typy
  • Konfigurace
  • Konverze
  • Funkce a operátory

O datu a čase

Jak asi víte, ne všude na zeměkouli je stejný čas. Když se řekne, že se něco stalo v 11 hodin, ale neřekne se v jaké časové zóně, tak vlastně nevíte, jak už je to dlouho. Časová zóna ale nestačí. Musíte ještě vědět, jestli se to stalo v letním (daylight-saving time) nebo zimním čase.

To, kde platí jaká časová zóna či letní/zimní čas je politické rozhodnutí, které se navíc v čase mění! Takže 13 hodin prvního června roku 2014 a tentýž čas a datum roku 1914 může být posunutý o hodinu. Aby toho nebylo málo, některé státy prochází několika časovými pásmy.

A to jsem se ještě nezmínil o tom, že taky existuje přestupný rok. Navíc, během století došlo k několika reformám kalendáře. Juliánský kalendář zavedl v Římě Gaius Julius Caesar roku 46 př. n. l. V r. 1582 vznikl reformou papeže Řehoře (Gregor) XIII. Gregoriánský kalendář, když rozdíl mezi kalendářním a slunečním počátkem roku dosáhl 10 dní. Počet přestupných let byl zredukován tak, že roky dělitelné stem jsou přestupné pouze jsou-li zároveň dělitelné čtyřmi sty. Gregoriánskou reformu kalendáře z roku 1582 přijala Velká Británie až o dvě století později a Rusko teprve po revoluci 1918. (Viz wikipedie). Ono to s tím datováním je vůbec velice zapeklité.

Taky je potřeba počítat s tím, že každý měsíc má jiný počet dní.

Jak vidíte, vůbec se nedá spolehnout na to, že den má 24 hodin, nebo rok 365 dní. Tyhle problémy jsou naštěstí známy a existují knihovny, které obsahují všechny potřebné informace o změnách času napříč státy i historií. Funkce a operátory v DBMS pro práci s časem by měli všechny problémy reflektovat. Vy jen musíte vědět, jakým způsobem je reflektují, abyste se nedivili, co že vám to vrací za výsledky.

Datové typy

Pro vytvoření datové či časové hodnoty existuje několik způsobů. Jejich kompletní popis najdete v dokumentaci k datovým typům. Nejbezpečnější je používat vždy ISO formát (viz ukázky v tabulce výše).

Interval můžete zadat nějak takto: INTERVAL '1 day 12 hours 59 min 10 sec'. Existují i další alternativní zápisy intervalu, které najdete v dokumentaci, ale tento je nejčitelnější a úplně si s ním vystačíte :-).

Interval se dá omezit, například:
INTERVAL '1 day 12 hours 59 min 10 sec' HOUR TO MINUTE
je totéž jako
INTERVAL '1 day 12 hours 59 min'
(ořezává se jen pravá – nejméně významná část z intervalu, takže se ořežou jen vteřiny a dny zůstávají, ikdyž bylo specifikováno „ponech hodiny až minut“).

Omezení intervalu může být: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, YEAR TO MONTH, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, HOUR TO MINUTE, HOUR TO SECOND, MINUTE TO SECOND.

Konfigurace

V některých zemích je běžné psát datum v pořádí měsíc, den, rok (MDY). U nás je to den, měsíc, rok (DMY). Toto nastavení se týká formátů datumu, který není jednoznačný (např. 1/2/1999 může být 1. února, nebo 2. ledna). V Postgresu si můžete nastavit jednu z těchto možností v jeho konfiguračním souboru volbou datestyle. Stejnou volbou se nastavuje i výstupní formát data (jak se datum zobrazí při SELECTu z databáze).
Ideálně byste měli mít nastaveno:
datestyle = 'iso, dmy'.

V konfiguračním souboru taky můžete nastavit defaultní časovou zónu (volbou timezone). Obvykle je nastavená tak, že se bere časová zóna z nastavení vašeho operačního systému.

Časovou zónu můžete nastavit SQL příkazem:

A zobrazit aktuální časovou zónu:

Seznam časových zón získáte z pohledu pg_timezone_names ze systémového schématu pg_catalog.

Použití datových typů

Jak už jsem psal, při použití datových typů času a data je nejdůležitější dávat si pozor na časové pásmo a letní/zimní čas.

Důležité je také vědět, že timestamp s časovou zónou se převede při ukládání na čas podle aktuálně nastavné časové zóny do UTC a při SELECTu zase zpět z UTC dle aktuální časové zóny. To má jeden neblahý(?) důsledek. Pokud se váš DBMS řídí časovou zónou operačního systému a nějaký nezkušený administrátor vám dočasně časovou zónu změní, může to vést k nemilému důsledku:

Zatím vypadá všechno sluníčkově. Datum bez časové zóny zobrazuje vždy stejný čas. Datum s časovou zónou taky, jen v různém formátu podle aktuální časové zóny. V praze zobrazí '12:00:00+01', v Londýně '11:00:00+00', což je to samé zapsané jinak.

Ošklivě ale narazíte, pokud budete převádět čas bez časové zóny na ten s časovou zónou, protože se v takovou chvíli použije aktuální časová zóna:

Překvapení! Čtvrtý řádek obsahuje jiný čas. Bylo totiž vloženo 12 hodin v Londýně, což je v Praze 13 hodin. Datum bez časové zóny časovou zónu ignoruje, takže ukazuje stabilně 12 hodin.

Práce s intervaly je jednoduchá. Jen se nesmí zapomínat na letní/zimní čas. Mimochodem, pro zadání časové zóny můžte použít kromě '+01' i jméno (Europe/Prague) nebo zkratku (CET).

Při použití časové zóny správně Postgres započítal přechod na letní čas a posunul hodiny o jednu dopředu (a změnil časovou zónu na +02 hodiny). Bez časové zóny prostě přičetl 14 hodin.

Všiměte si, že přičíst 1 den a 24 hodin není vždy totéž. To platí i o roku a 365 dnech (kvůli přestupnému roku), ale neplatí o týdnu a 7 dnech (to je vždy to samé).

Speciální hodnoty

Při zadávání data nebo času můžete použít některou z následujících speciálních hodnot. Hodnoty se při použití uzavírají do uvozovek.

Formátování a konverze

Postgres mnohdy dokáže převést text na TIMESTAMP implicitně. Takže můžete insertovat hodnotu jako '2014-01-24 13:30:00 CET' bez explicitní konverze na TIMESTAMP.

Postgres dokáže automaticky převést jen některé způsoby zápisu data a jak datum převede je navíc závislé na konfiguraci. Nejbezpečnější je použít ISO formát (viz příklad v předchozím odstavci), ale někdy můžete potřebovat převést i jiný formát zápisu.

K převodu textu na datum/čas a obráceně slouží funkce TO_CHAR a TO_TIMESTAMP. Pomocí TO_TIMESTAMP taky můžete převést unixový čas (počet vteřin od 1970-01-01 00:00:00+00) na TIMESTAMP.

Druhý argument TO_CHAR a TO_TIMESTAMP můžete poskládat z různých zástupných zkratek pro formát data a času. Všechny je najdete v dokumentaci v tabulce Template Patterns for Date/Time Formatting.

Všiměte si, že TO_TIMESTAMP vrací i časovou zónu, ikdyž jsem jí v prvním argumentu nijak nespecifikoval. On si jí prostě vzal z aktuálního nastavení DBMS. Takže, když budete mít špatně nastavenou časovou zónu a budete jí používat …

Funkce EXTRACT se používá pro získání části data. Můžete jí použít pro získání unixového času (počtu vteřin od začátku „epochy“).

Funkce EXTRACT dokáže vyextrahovat z TIMESTAMPu téměř cokoliv. Podívejte se do dokumentace na to, co všechno můžete extrahovat. Máte tam i spoustu příkladů.

Ke konverzi mezi datovými typy můžete použít funkci CAST. Ta se používá na konverzi všemožných datových typů, teď jí ale ukáži při práci s datovými typy pro čas a datum. A nezapomeňte (zase) na vliv časové zóny.

K povídání o konverzi asi ještě patří funkce DATE_TRUNC. Ta ořeže z data „méně významnou část“ (vteřiny jsou méně významné než minuty atd.).

Pokud jde o ořezávání, tak ořezat můžete i interval, jak jsem už psal v části o datových typech.

Poslední možností konverze je zobrazení TIMESTAMPu v zadané časové zóně pomocí AT TIME ZONE.

Všiměte si, jak se v příkladech změní čas bez časové zóny na ten s časovou zónou a naopak.

Funkce a operátory

V Postgresu můžete k datům přičítat a odečítat intervaly, odečítat data od sebe (výsledkem je interval), násobit nebo dělit intervaly (3 * 1 vteřina jsou 3 sekundy). Postgres také poskytuje sadu zajímavých funkcí pro práci s časem a datumem. Všechno najdete i s příklady v dokumentaci.

Pro ty zvás, co se bojí kliknout, nebo mají fobii z angličtiny (ale ty příklady pochopíte i bez ní, fakt), jsem sem obšlehnul pár zajímavých příkladů:

Zajímavé je i to, co je to aktuální čas. Někdy je to čas, kdy začala transakce (funkce v rámci jedné transakce vrací stále stejný čas, bez ohledu na to, kolik už času uběhlo od začátku transakce), někdy je to čas kdy byl spuštěn SQL příkaz (v rámci transakce každé spuštění příkazu bude mít vlastní čas (stejný v rámci jednoho SQL příkazu)), nebo skutečný aktuální čas (pak i v jednom SQL příkazu může vrátit funkce různý čas).

Tyto funkce vrací vždy čas začátku transakce:

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
LOCALTIME
LOCALTIMESTAMP

U těchto funkcí na to přijdete z jejich jména (až na NOW() – ta je ekvivalentní s transaction_timestamp()):

now()
transaction_timestamp()
statement_timestamp()
clock_timestamp()

Všechny funkce a datové typy můžete používat jako DEFAULTNí hodnotu. Jen pozor na rozdíl v tomto:

Všechny 3 řádky vrací to samé. Třetí řádek ovšem není funkce, ale speciální hodnota. Takže, když použijete TIMESTAMP 'now', budete mít jako defaultní hodnotu datum a čas z doby vytvoření tabulky a ne aktuální čas v době provedení INSERTu (nebo UPDATU).

MySQL

Tahle kapitola už je dost dlouhá a aby toho nebylo málo, tak v MySQL (a nejen v MySQL) je všechno jinak. Snažit se popsat jen to, co je v MySQL jinak, by teď nebylo zrovna přehledný – skoro všechno je jinak. Proto popíšu jak to v MySQL je a abych kapitolu moc nenatahoval, pokusím se MySQL popsat tak stručně, jak to jen jde. Vy už víte, na co si musíte při práci s datem a časem dávat pozor. Když si nebudete něčím v MySQL jistí, vyzkoušejte si to, nebo si to nastudujte z dokumentace.
Toto platí i pro ostatní DBMS (SQLite a Oracle) samozřejmě taky.

Datové typy

V MySQL jsou pro datum a čas k dispozici tři datové typy: DATE, DATETIME a TIMESTAMP. První dva jsou bez časové zóny, TIMESTAMP s časovou zónou. (Ale na rozdíl od PostgreSQL se neuvádí WITH TIME ZONE.)

Datumy mohou obsahovat zlomky sekund na 6 desetinných míst (v Postgresu jen na 3), mohou se zadávat v ISO formátu, třeba takto: '2010-12-10 14:12:09.019473'. Existují i další způsoby zápisu dat, které MySQL dokáže rozšifrovat, doporučuju ale držet se tohoto způsobu.

U MySQL můžete před datumem/časem zadat datový typ DATE, TIME (ikdyž datový typ TIME nezná) a TIMESTAMP, protože to vyžaduje SQL standard. MySQL tento datový typ ignoruje, takže ho nepoužívejte, ať vás to nemate.

Datovou zónu zadat nemůžete. Bere se z aktuálně nastavené datové zóny (pro TIMESTAMP, ostatní datové typy (DATE a DATETIME) datovou zónu ignorují).

Konfigurace

MySQL bere časovou zónu z operačního systému. Můžete si jí v konfiguračním souboru i pomocí SQL příkazu nastavit, ale nejdřív musíte do MySQL časové zóny nahrát. MySQL má sice v schématu mysql připravené tabulky (time_zone*), ale ty jsou prázdné.

K naplnění těchto tabulek slouží program mysql_tzinfo_to_sql. Pokud používáte Linux, předáte mu na příkazové řádce cestu k adresáři s informacemi o časových zónách (obvykle /usr/share/zoneinfo) a jeho výstup přesměrujete do klienta mysql.

Pokud používáte Windows, můžete si stáhnout soubory s časovými zónami zde.

Po nahrání časových zón si je můžete zobrazit třeba takto:

Zobrazení a nastavení časové zóny aktuálního spojení se v MySQL provede takto:

Další informace o časových zónách v MySQl viz MySQL Server Time Zone Support.

TIMESTAMP

TIMESTAMP má v MySQL několik zajímavých vlastností. Předně si dávejte pozor na časovou zónu:

Myslím, že výsledek už dokážete interpretovat sami. První řádek vložil čas v časové zóně 'Europe/Prague', ale při SELECTu byla nastavená zóna Europe/London …

Časová zóna se nedá v MySQL zadat explicitně, vždy se bere z aktuálního nastavení:

TIMESTAMP jako jediný datový typ může mít jako defaultní hodnotu aktuální čas. Dokonce se automaticky aktualizuje na aktuální čas i v případě UPDATE řádku (pokud jsou aktualizovány ostatní sloupce). Toto chování můžete nastavit explicitně. Když ale nenastavíte ani defaultní hodnotu, ani automatický update, tak se tak chová TIMESTAMP stejně.

Následující definice tabulek jsou totožné:

Pouze jeden slopec v tabulce může mít nastavenou defaultní hodnotu nebo automatický UPDATE na aktuální čas. To neznamená, že byste nemohli mít více sloupečků s datovým typem TIMESTAMP. Pokud se nepokusíte uvést něco jinak, tak automatický update má pouze první sloupeček s TIMESTAMP, ostatní ne. Pokud chcete mít defaultní hodnotu nebo automatický UPDATE na jiném než prvním sloupečku, musíte u toho prvního explicitně automatické hodnoty vypnout a u druhého zase zapnout. Například, když nastavíte defaultní hodnotu na 0, nebude fungovat ani ON UPDATE CURRENT_TIMESTAMP.

Pokud u datového typu TIMESTAMP neurčíte explicitně že může obsahovat NULL, tak NULL obsahovat nemůže. Pokud budete do takového sloupce vkládat NULL, vloží se místo něj defaultní hodnota (aktuální čas).

Podrobné pravidal o tom, kdy se používají autmoatické hodnoty a kdy ne najdete v dokumentaci Automatic Initialization and Updating for TIMESTAMP.

Funkce

MySQL obsahuje hodně funkcí pro práci s časem a datem. Chybí jí snad jen nějaké funkce na výpočet věku.

V tabulce je seznam těch nejdůležitějích. Další najdete v dokumentaci Date and Time Functions.

Příklady použití funkcí

SQLite

SQLite poskytuje jen 2 datové typy pro práci s časem a datumem: DATE a DATETIME. Oba se ukládají interně jako číslo.

Funkce pro práci s časem je v zásadě jen jedna: stftime. Existují ještě další funkce, které jsou ale jen zkrácený zápis stftime.

timestring je nejjednodušší zadávat ve formátu 'YYYY-MM-DD HH:MM:SS.SSS', tedy například '2014-01-30 13:30:25.155', kde poslední 3 čísla jsou zlomky sekund.

modifier je něco podobného jako interval v Postgresu. Třeba následující příklad zobrazí poslední den aktuálního měsíce:

Kompletní seznam modifikátorů najdete v dokumentaci.

format pro funkci strftime je stejný, jako pro funkci strftime z jazyka C. Možnosti substitučních formátů najdete i v dokumentaci SQLite. Funkce strftime z SQLite má navíc substituční formát pro frakci sekund %f.

V SQLite se na časové zóny moc nehraje. Můžete připsat na konec času posun od UTC času ve formátu +-HH:MM. Hodnota se k datu a času přičte (či odečte), výsledek se vždy ukládá v UTC. Letní/zimní čas už se vůbec nebere v potaz.

Rozdíl oproti Postgresu (i MySQL) je i při přičítání 1 měsíce. SQLite prostě zvýší hodnotu měsíce o jedna a pak datum normalizuje. Takže 2001-03-31 + 1 měsíc je 2001-04-31, ale protože duben nemá 31 dní, výsledkem je 2001-05-01. V Postgresu (i MySQL) by byl výsledek 2001-04-30. (Postgres i MySQL vrací tento výsledek pro dva různé datumy, všimli jste si?)

Pokud jde o extrakci části datumu, nebo konverzi datumu, tak na to si musíte vystačit s funkcí strftime.

Oracle

Datové typy

V Oracle se defaultně zobrazuje a zadává DATE ve formátu DD-MON-RR, tj. například 24-JAN-14, přestože uchovává i čas.

Další možnosti zadání data jsou:

První řádek je dle ANSI normy. Druhý řádek využívá funkce TO_DATE. Třetí parametr je tam kvůli určení jazyka, ve kterém je název měsíce (DEC je americká zkratka pro prosinec).
Poslední tři řádky jsou taky zadání dle ANSI normy. Třetí řádek ukazuje zadání TIMESTAMP s přesností na destinu vteřiny, čtvrtý a pátý dva způsoby, jak je možné zadat časovou zónu.

Intervaly můžete zadat takto:

Pravidla pro zadávání roku dvěma číslicemi jsou:

Konfigurace

Podporované časové zóny zjistíte tímto příkazem:

Defaulní časový formát (pro typ DATE je to 'DD-MM-RR') lze nastvit pro aktuální sezení takto:

Nastavené formáty lze zjisit takto:

Změna časové zóny pro aktuální sezení(vyberte si dle libosti):

V Oracle se časová zóna nastavuje na úrovni databáze. Při vytvoření databáze se převezme z operačního systému, nebo jí můžete určit explicitně:

Později se dá změnit (pokud na to máte dostatečná práva):

Nastavenou časovou zónu databáze (ne platnou pro aktuální sezení) zjistíte takto:

Konverze

Konverze datových typů

Funkce pro konverzi můžete vidět na obrázku. Pro práci s datem jsou důležité TO_DATE a TO_CHAR. Co můžete použít pro formát data najdete v dokumentaci.

Například: DD je den v měsíci (1-31), MON je zkratka názvu měsíce, YYYY je nezkrácený rok.

Třetí parametr nemusíte uvádět, pokud se spolehnete na aktuální nastevní sezení. To zjistíte takto:

Pro další datové typy existují obdobné funkce TO_TIMESTAMP a TO_TIMESTAMP_TZ.

Stejně jako v Postgresu můžete použít AT TITME ZONE:

Funkce FROM_TZ umožňuje připojit k TIMESTAMPu datovou zónu (a vytvořit tak TIMESTAMP WITH TIME ZONE).

Funkce TO_CHAR umožňuje vypsat datum v zadaném formátu.

Funkce a operátory

Přičtení čísla k datu se chápe jako přičtení počtu dnů:

Můžete přičítat i intervaly, podobně jako v Postgresu:

Funkce pro práci s časem

Příklady

Další funkce

Příklady