Datum a čas
Při práci s časem a datumem si musíte dávat pozor ně několik věcí. Jendnak na časovou zónu
(časová pásma, chcete-li), na letní a zimní čas, na konverzi typů s a bez časovou zónou,
na nastavení časové zóny v konfiguraci DBMS atd. O tom všem je tato kapitola.
Kapitola je nechutně dlouhá, ale to proto, že se v každé databázi řeší práce s datumy
dost odlišně. Nastudujete si určitě část o Postgresu, ostatní kapitoly už nepopisují
podrobně všechny problémy, na které lze narazit.
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.
Letní čas začíná poslední březnový víkend (v noci ze soboty na neděli) a končí poslední víkendu v říjnu. Takže rok od roku jindy.
Časové pásmo se udává v posunu od UTC (Coordinated Universal Time), též nazývaného GMT, Greenwich Mean Time. (Nultý poledník prochází Královskou observatoří v Greenwichi v Londýně). V ČR platí časové pásmo +1 hodina, nazývané 'Europe/Prague'.
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.
Věděli jste, že existují i přestupné sekundy?
Datové typy
Datový typ | Význam | Popis |
---|---|---|
timestamp [ without time zone ] | Datum a čas bez časové zóny | 014-01-22 12:00:00 |
timestamp with time zone | Datum a čas s časovou zónou | 2014-01-22 12:00:00+01 |
date | Datum (bez času) | 2014-01-22 |
time [ without time zone ] | Čas (bez data) | 23:00:00 |
time with time zone | Čas (bez data) s časovou zónou | Postgres jej podporuje jen proto, že jej vyžaduje standard. Nedoporučuje se používat, protože čas s časovou zónou bez datumu je na prd. |
interval | Časový interval. | Používá se pro aritmetiku s časem (přičítání, odčítání časového intervalu, výsledek rozdílu dvou času atp.). |
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'
.
Nastavení | Příklad |
---|---|
iso | 1997-12-17 07:37:16-08 |
sql | 12/17/1997 07:37:16.00 PST |
postgres | Wed Dec 17 07:37:16 1997 PST |
german | 17.12.1997 07:37:16.00 PST |
PTS a -08 je označení časové zóny.
Umístění konfiguračního souboru PostgreSQL zjistíte SQL příkazem SHOW config_file;
.
Musíte k tomu mít oprávnění superuživatele. Ty získáte nejsnadněji tak, že se přihlásíte k
databázi jako uživatel postgres
. (V Linuxu se pomocí příkazu
sudo su postgres
přepněte na účet postgres
(zadejte heslo roota)
a spusťte psql
.)
V mé distribuci je konfigurační soubor Postgresu /var/lib/pgsql/data/postgresql.conf
.
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
.
name | abbrev | utc_offset | is_dst
----------------------------------------+--------+------------+--------
Etc/GMT+12 | GMT+12 | -12:00:00 | f
posix/Etc/GMT+12 | GMT+12 | -12:00:00 | f
posix/Pacific/Niue | NUT | -11:00:00 | f
...
GMT | GMT | 00:00:00 | f
GMT+0 | GMT | 00:00:00 | f
Europe/London | GMT | 00:00:00 | f
...
CET | CET | 01:00:00 | f
Europe/Bratislava | CET | 01:00:00 | f
Europe/Prague | CET | 01:00:00 | f
...
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:
rimmer1=> SET TIMEZONE TO 'Europe/Prague';
rimmer1=> INSERT INTO zonetest(datumz,datum) VALUES('2014-01-21 12:00:00+01','2014-01-21 12:00:00');
rimmer1=> SET TIMEZONE TO 'Europe/London';
rimmer1=> INSERT INTO zonetest(datumz,datum) VALUES('2014-01-21 12:00:00+01','2014-01-21 12:00:00');
rimmer1=> SET TIMEZONE TO 'Europe/Prague';
rimmer1=> SELECT * FROM zonetest ORDER BY id;
id | datumz | datum
----+------------------------+---------------------
1 | 2014-01-21 12:00:00+01 | 2014-01-21 12:00:00
2 | 2014-01-21 12:00:00+01 | 2014-01-21 12:00:00
(2 řádky)
rimmer1=> SET TIMEZONE TO 'Europe/London';
rimmer1=> SELECT * FROM zonetest ORDER BY id;
id | datumz | datum
----+------------------------+---------------------
1 | 2014-01-21 11:00:00+00 | 2014-01-21 12:00:00
2 | 2014-01-21 11:00:00+00 | 2014-01-21 12:00:00
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:
rimmer1=> INSERT INTO zonetest(datumz, datum) VALUES('2014-01-21 12:00:00','2014-01-21 12:00:00+01');
rimmer1=> SET TIMEZONE TO 'Europe/London';
rimmer1=> INSERT INTO zonetest(datumz, datum) VALUES('2014-01-21 12:00:00','2014-01-21 12:00:00+01');
rimmer1=> SET TIMEZONE TO 'Europe/Prague';
rimmer1=> SELECT * FROM zonetest;
id | datumz | datum
----+------------------------+---------------------
1 | 2014-01-21 12:00:00+01 | 2014-01-21 12:00:00
2 | 2014-01-21 12:00:00+01 | 2014-01-21 12:00:00
3 | 2014-01-21 12:00:00+01 | 2014-01-21 12:00:00
4 | 2014-01-21 13:00:00+01 | 2014-01-21 12:00:00
(4 řádky)
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.
Pokud budete někdy něco programovat a budete pracovat s datumy s časovou zónou, nespoléhejte na to, že máte vždy nastavené pásmo 'Europe/Prague' a informaci o časovém posunu neignorujte (ani při čtení, ani při zápisu). Dříve nebo později na to dojedete.
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).
TIMESTAMP WITH TIME ZONE '2014-03-29 12:00:00 Europe/Prague' + INTERVAL '13 hour' AS "+13",
TIMESTAMP WITH TIME ZONE '2014-03-29 12:00:00 Europe/Prague' + INTERVAL '14 hour' AS "+14";
+13 | +14
------------------------+------------------------
2014-03-30 01:00:00+01 | 2014-03-30 03:00:00+02
(1 řádka)
rimmer1=> SELECT
TIMESTAMP '2014-03-29 12:00:00' + INTERVAL '13 hour' AS "+13",
TIMESTAMP '2014-03-29 12:00:00' + INTERVAL '14 hour' AS "+14";
+13 | +14
---------------------+---------------------
2014-03-30 01:00:00 | 2014-03-30 02:00:00
(1 řádka)
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é).
TIMESTAMP WITH TIME ZONE '2014-03-29 12:00:00+01' + INTERVAL '24 hour' AS "24 hodin",
TIMESTAMP WITH TIME ZONE '2014-03-29 12:00:00+01' + INTERVAL '1 day' AS "1 den";
24 hodin | 1 den
------------------------+------------------------
2014-03-30 13:00:00+02 | 2014-03-30 12:00:00+02
(1 řádka)
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.
Hodnota | Použitelné s | Popis |
---|---|---|
epoch | date, timestamp | 1970-01-01 00:00:00+00 (UNIXový systémový čas se ukládá jako počet vteřin od tohoto data) |
infinity | date, timestamp | "Nejpozdější" čas |
-infinity | date, timestamp | "Nejdřívější" čas |
now | date, time, timestamp | Začátek aktuální transakce |
today | date, timestamp | dnešní půlnoc |
tomorrow | date, timestamp | zítřejší půlnoc |
yesterday | date, timestamp | včerejší půlnoc |
allballs | time | 00:00:00.00 UTC |
Tyto hodnoty není vhodné používat jako defaultní hodnoty. Viz funkce.
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
.
TO_CHAR(TIMESTAMP WITH TIME ZONE '2014-01-24 13:30:01.15+01',
'Day, DD Month YYYY HH24:MI:SS.MS') AS text;
text
-------------------------------------------
Friday , 24 January 2014 13:30:01.150
rimmer1=> SELECT
TO_TIMESTAMP('Friday, 24 January 2014 13:30:01.150',
'Day, DD Month YYYY HH24:MI:SS.MS') AS timestamp;
timestamp
---------------------------
2014-01-24 13:30:01.15+01
(1 řádka)
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 …
Když napíšu TIMESTAMP WITH TIME ZONE '2014-01-24 13:30:01.15+01'
tak Postgresu
říkám, aby ten text (co je v uvozovkách) implicitně převedl na TIMESTAMP
s časovou zónou. Kdybych napsal jen TIMESTAMP '2014-01-24 13:30:01.15+01'
,
bude Postgres časovou zónu ignorovat.
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.).
Co všechno můžete ořezat najdete, jako vždy, v dokumentaci.
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
.
rimmer1=> SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Europe/London';
timezone
------------------------
2001-02-16 21:38:40+01
(1 řádka)
rimmer1=> SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+01' AT TIME ZONE 'GMT'; -- totez co 'Europe/London'
timezone
---------------------
2001-02-16 19:38:40
(1 řádka)
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ů:
DATE '2001-09-28' + INTEGER '7' AS soucet,
TIMESTAMP '2001-09-29 03:00' - TIMESTAMP '2001-09-27 12:00' AS rozdil,
21 * INTERVAL '1 day' AS nasobeni;
soucet | rozdil | nasobeni
------------+----------------+----------
2001-10-05 | 1 day 15:00:00 | 21 days
(1 řádka)
rimmer1=> SELECT age('1979-05-14 04:30:00',now()), CURRENT_TIMESTAMP, LOCALTIMESTAMP;
age | now | timestamp
---------------------------------------------+-------------------------------+----------------------------
-34 years -8 mons -10 days -09:15:26.609082 | 2014-01-24 13:45:26.609082+01 | 2014-01-24 13:45:26.609082
(1 řádka)
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.
DATE '2010-12-10 14:12:09.019473' AS "date",
TIME '2010-12-10 14:12:09.019473' AS "time",
TIMESTAMP '2010-12-10 14:12:09.019473' AS "timestamp";
+----------------------------+----------------------------+----------------------------+
| date | time | timestamp |
+----------------------------+----------------------------+----------------------------+
| 2010-12-10 14:12:09.019473 | 2010-12-10 14:12:09.019473 | 2010-12-10 14:12:09.019473 |
+----------------------------+----------------------------+----------------------------+
1 row in set (0.00 sec)
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
.
Tento program by měl být ve vaší linuxové distribuci. V OpenSuSE jsem musel
doinstalovat balíček mysql-community-server-tools
, v Debianu
byl součástí balíčku mysql-server
, takže jsem nic doinstalovávat nemusel.
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:
FROM time_zone_transition_type t JOIN time_zone_name n
ON t.Time_zone_id = n.Time_zone_id
WHERE Name = 'Europe/Prague';
+---------------+--------+--------+--------------+
| Name | Offset | is_DST | Abbreviation |
+---------------+--------+--------+--------------+
| Europe/Prague | 7200 | 1 | CEST |
| Europe/Prague | 3600 | 0 | CET |
| Europe/Prague | 7200 | 1 | CEST |
| Europe/Prague | 3600 | 0 | CET |
| Europe/Prague | 7200 | 1 | CEST |
| Europe/Prague | 3600 | 0 | CET |
+---------------+--------+--------+--------------+
6 rows in set (0.00 sec)
Offset
je posun od UTC ve vteřinách a is_DST
říká, jestli je
časová zóna letní čas (daylight-saving time).
Zobrazení a nastavení časové zóny aktuálního spojení se v MySQL provede takto:
+---------------------+
| @@session.time_zone |
+---------------------+
| SYSTEM |
+---------------------+
-- SYSTEM znamená, že se bere zóna z operačního systému
mysql> SET time_zone = 'Europe/Prague';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'Europe/Prague'
-- první pokus před nahráním časových zón selhal
mysql> SET time_zone = 'Europe/Prague';
Query OK, 0 rows affected (0.00 sec)
-- po nahrání časových zón (pomocí mysql_tzinfo_to_sql)
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:
mysql> SET time_zone = "Europe/Prague";
mysql> INSERT INTO zonetest(datumz, datum) VALUES('2014-01-21 12:00:00', '2014-01-21 12:00:00');
mysql> SET time_zone = "Europe/London";
mysql> INSERT INTO zonetest(datumz, datum) VALUES('2014-01-21 12:00:00', '2014-01-21 12:00:00');
mysql> SELECT * FROM zonetest ORDER BY id;
+----+---------------------+---------------------+
| id | datumz | datum |
+----+---------------------+---------------------+
| 1 | 2014-01-21 11:00:00 | 2014-01-21 12:00:00 |
| 2 | 2014-01-21 12:00:00 | 2014-01-21 12:00:00 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)
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í:
mysql> INSERT INTO zonetest(datumz, datum) VALUES('2014-01-21 12:00:00+01:00', '2014-01-21 12:00:00+01:00');
SHOW warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'datumz' at row 1 |
| Warning | 1264 | Out of range value for column 'datum' at row 1 |
+---------+------+------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * from zonetest WHERE id = 3;
+----+---------------------+---------------------+
| id | datumz | datum |
+----+---------------------+---------------------+
| 3 | 2014-01-21 12:00:00 | 2014-01-21 12:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
Kdyby se časová hodnota (12 hodin) uložila s časovou zónou +01, pak by se musela v nastavené časové zóně "Europe/London" zobrazit jako 11 hodin. Ve skutečnosti se uložilo 12 hodin v nastavené časové zóně ("Europe/London").
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
.
mysql> INSERT INTO t3 (cislo) VALUES (1);
mysql> SELECT * FROM t3;
+-------+---------------------+---------------------+
| cislo | datum1 | datum2 |
+-------+---------------------+---------------------+
| 1 | 0000-00-00 00:00:00 | 2014-01-24 20:18:10 |
+-------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> UPDATE t3 SET datum1 = CURRENT_TIMESTAMP, cislo = 2;
mysql> SELECT * FROM t3;
+-------+---------------------+---------------------+
| cislo | datum1 | datum2 |
+-------+---------------------+---------------------+
| 2 | 2014-01-24 20:20:21 | 2014-01-24 20:20:21 |
+-------+---------------------+---------------------+
1 row in set (0.00 sec)
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).
mysql> SELECT * FROM t3;
+-------+---------------------+---------------------+
| cislo | datum1 | datum2 |
+-------+---------------------+---------------------+
| 2 | 2014-01-24 20:21:07 | 2014-01-24 20:21:07 |
| 3 | 2014-01-24 20:23:04 | 2014-01-24 20:23:04 |
+-------+---------------------+---------------------+
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.
Funkce | Význam |
---|---|
CONVERT_TZ | Konvertuje z jedné časové zóny do jiné. |
NOW(), CURRENT_TIMESTAMP | Vrací čas začátku transakce (v aktuální časové zóně) |
SYSDATE() | Vrací čas ve chvíli spuštění funkce (takže může vrátit v rámci jedno SQL dotazu různé časy). |
UTC_TIMESTAMP() | Vrací aktuální čas v UTC zóně |
DATE_ADD() | Přičítá interval k datu |
DATE_SUB() | Odečítá interval od data |
DATE() | Extrahuje pouze datovou část z DATETIME nebo TIMESTAMPu |
DATEDIFF() | Odečte dvě data a vrátí rozdíl v počtu dní |
EXTRACT() | Extrahuje zadanou část z data. Výrazy pro extrahované části data jsou stejné, jako pro interval, viz DATE_ADD() |
FROM_UNIXTIME() | Převede číslo na datum (jako počet vteřin od 1970-01-01) |
UNIX_TIMESTAMP() | Převede datum na číslo (jako počet vteřin od 1970-01-01) |
DATE_FORMAT() | Naformátuje datum podle zadaného formátu. |
STR_TO_DATE() | Inverzní funkce k DATE_FORMAT() (převede řetězec na datový typ DATETIME, DATE nebo TIME) |
Příklady použití funkcí
mysql> SELECT
CONVERT_TZ('2014-01-01 12:00:00','SYSTEM','Europe/London') as PrgToLon;
+---------------------+
| PrgToLon |
+---------------------+
| 2014-01-01 11:00:00 |
+---------------------+
mysql> SELECT NOW(), UTC_TIMESTAMP;
+---------------------+---------------------+
| NOW() | UTC_TIMESTAMP |
+---------------------+---------------------+
| 2014-01-24 21:27:24 | 2014-01-24 20:27:24 |
+---------------------+---------------------+
mysql> SELECT
DATE_ADD('2014-03-29 12:00:00', INTERVAL 13 hour) as "+13",
DATE_ADD('2014-03-29 12:00:00', INTERVAL 14 hour) as "+14";
+---------------------+---------------------+
| +13 | +14 |
+---------------------+---------------------+
| 2014-03-30 01:00:00 | 2014-03-30 02:00:00 |
+---------------------+---------------------+
mysql> SELECT
DATE_ADD('2014-01-24 12:00:00', INTERVAL -11 hour) as "-11",
DATE_SUB('2014-01-24 12:00:00', INTERVAL 11 hour) as "-11";
+---------------------+---------------------+
| -11 | -11 |
+---------------------+---------------------+
| 2014-01-24 01:00:00 | 2014-01-24 01:00:00 |
+---------------------+---------------------+
mysql> SELECT
DATE('2014-01-24 12:00:00') as date,
DATEDIFF('2014-01-24 11:00:00', '1989-11-17 23:00:00') as datediff;
+------------+----------+
| date | datediff |
+------------+----------+
| 2014-01-24 | 8834 |
+------------+----------+
mysql> SELECT
EXTRACT(YEAR FROM '2014-01-24 12:00:00') AS year,
EXTRACT(DAY_MINUTE FROM '2014-01-24 12:30:00') AS "day_minute";
+------+------------+
| year | day_minute |
+------+------------+
| 2014 | 241230 |
+------+------------+
mysql> SELECT
UNIX_TIMESTAMP('2014-03-30 02:00:00') as "02",
UNIX_TIMESTAMP('2014-03-30 03:00:00') as "03",
FROM_UNIXTIME(1396141200) as "from_unixtime";
+------------+------------+---------------------+
| 02 | 03 | from_unixtime |
+------------+------------+---------------------+
| 1396141200 | 1396141200 | 2014-03-30 03:00:00 |
+------------+------------+---------------------+
mysql> SET time_zone = 'UTC';
mysql> SELECT
UNIX_TIMESTAMP('2014-03-30 02:00:00') as "02",
UNIX_TIMESTAMP('2014-03-30 03:00:00') as "03",
FROM_UNIXTIME(1396141200) as "from_unixtime";
+------------+------------+---------------------+
| 02 | 03 | from_unixtime |
+------------+------------+---------------------+
| 1396144800 | 1396148400 | 2014-03-30 01:00:00 |
+------------+------------+---------------------+
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') as format;
+---------------------+
| format |
+---------------------+
| Sunday October 2009 |
+---------------------+
mysql> SELECT
STR_TO_DATE('04/31/2004 13:35:10.1275', '%m/%d/%Y %H:%i:%s.%f') as "date";
+----------------------------+
| date |
+----------------------------+
| 2004-04-31 13:35:10.127500 |
+----------------------------+
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
.
funkce | Ekvivalent pomocí strftime |
---|---|
strftime(format, timestring, modifier, modifier, ...) |
|
date(timestring, modifier, modifier, ...) |
strftime('%Y-%m-%d',timestring, modifier, modifier, ...) |
time(timestring, modifier, modifier, ...) |
strftime('%H:%M:%S',timestring, modifier, modifier, ...) |
datetime(timestring, modifier, modifier, ...) |
strftime('%Y-%m-%d %H:%M:%S',timestring, modifier, modifier, ...) |
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.
8 hodin v našem časovém pásmu je 7 hodin v UTC.
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
Datový typ | Význam |
---|---|
DATE | Datum i čas bez časové zóny. Rozsah od 1. ledna 4712 B.C. do 31 prosince 9999 |
TIMESTAMP | Datum i čas bez časové zóny, oproti DATE ukládá i zlomky sekund |
TIMESTAMP WITH TIME ZONE | Ukládá se i časová zóna. |
TIMESTAMP WITH LOCAL TIME ZONE | Čas se převádí do UTC a z UTC jako v Postgresu u TIMESTAMP WITH TIME ZONE. (Takže původní časovou zónu už nejde zjistit). |
INTERVAL YEAR TO MONTH | Interval obsahující rok a měsíc. Jedná se skutečně o datový typ (můžete vytvořit tabulku se sloupečkem s tímto datovým typem). |
INTERVAL DAY TO SECOND | Interval obsahující den, hodinu, minutu, vteřinu a zlomky vteřin. |
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:
Pokud je zadaný rok v rozsahu: | |||
---|---|---|---|
0-49 | 50-99 | ||
Pokud aktuální rok končí na: |
0-49 | Vrácený datum je v aktuálním století |
Vrácený datum je v předházejícím století |
50-99 | Vrácený datum je v následujícím století |
Vrácený datum je v aktuálním století |
Konfigurace
Podporované časové zóny zjistíte tímto příkazem:
CET | Central Europe Time |
---|---|
CEST | Central Europe Summer Time (letní čas) |
Defaulní časový formát (pro typ DATE je to 'DD-MM-RR') lze nastvit pro aktuální sezení takto:
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF6'; -- '2015-10-02 13:10:10.123456'
...
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
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:
Všiměte si, že číselný údaj v intervalu musí být v uvozovkách.
Funkce pro práci s časem
Funkce | Popis |
---|---|
SYSDATE | SYSDATE vrátí aktuálni čas počítače, na kterém běží databáze. Časovou zónu bere dle nastavení počítače v době spuštění databáze (takže ne časovou zónu nastavenou pro aktuální spojení). |
CURRENT_DATE | Vrátí aktuální čas dle aktuálně nastavené časové zóny typu DATE. |
CURRENT_TIMESTAMP | Jako CURRENT_DATE, ale vrací TIMESTAMP WITH TIME ZONE |
LOCALTIMESTAMP | Jako CURRENT_DATE, ale vrací TIMESTAMP |
EXTRACT (xxx FROM datetime) | Extrahuje část data. xxx může být: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,
TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR).
|
Příklady
oracle> SELECT
TO_CHAR(SYSDATE,'YYYY-MON-DD HH24:MI:SS') "Pocitac",
TO_CHAR(CURRENT_DATE, 'YYYY-MON-DD HH24:MI:SS') "Hong_Kong"
FROM dual;
Pocitac Hong_Kong
----------------------------- -----------------------------
2014-LED-26 16:07:11 2014-LED-26 23:07:11
Další funkce
Funkce | Popis |
---|---|
MONTHS_BETWEEN | Počet měsíců mezi dvěmi daty |
ADD_MONTHS | Přidá měsíce k datu |
NEXT_DAY | Další den od zdaného data |
LAST_DAY | Poslední den měsíce |
ROUND | Zaokrouhlí datum dle zadané „přesnosti“ |
TRUNC | Vrátí z data pouze rok, měsíc a den. |
Příklady
NEXT_DAY('21-DUB-14','Čtvrtek') as next,
ROUND (TO_DATE('24-DUB-14'), 'MONTH') AS round_month,
ROUND (TO_DATE('24-SRP-14'), 'YEAR') AS round_year,
TRUNC (TO_DATE('24-DUB-14'), 'MONTH') AS trunc_month,
TRUNC (TO_DATE('24-DUB-14'), 'YEAR') AS trunc_year
FROM dual;
NEXT ROUND_MO ROUND_YE TRUNC_MO TRUNC_YE
-------- -------- -------- -------- --------
24.04.14 01.05.14 01.01.15 01.04.14 01.01.14