Nebojte se formátu datumu a času v MySQL

Používáte-li MySQL databáze, můžete využít užitečné funkce SQL při práci s datumem a časem. Před tím byste ale měli dodržet formátování typické pro SQL.

Pro začátek připomenu, v jakém formátu pracujeme v MySQL s datumem a časem:

YEAR(M) – rozmezí ‚1901‘ až ‚2155‘ (pro čtyřmístný formát) nebo ’70‘ až ’69‘ (pro dvoumístný formát 1970-2069), jako argument můžeme použít 2 nebo 4 (bez argumentu 4); ve formátu rrrr nebo rr;

DATE – rozmezí ‚1000-01-01‘ až ‚9999-12-31‘; ve formátu rrrr-mm-dd

TIME – ‚-838:59:59‘ až ‚838:59:59‘; formát hh:mm:ss;

DATETIME – rozmezí ‚1000-01-01 00:00:00‘ až ‚9999-12-31 23:59:59‘; ve formátu rrrr-mm-dd hh:mm:ss;

TIMESTAMP (M) – rozmezí ‚1970-01-01 00:00:00‘ až ‚2037-xx-xx xx:xx:xx‘ bez oddělovacích znamének a mezer. Jako argument (M) můžeme zadat délku údaje, který chceme získat 14, 12, 8 nebo 6, formát (podle argumentu) rrrrmmddhhmmss, rrmmddhhmmss, rrrrmmdd nebo rrmmdd; pokud nezadáme argument, získáme 14místný zápis.

Pokud chceme zjistit časové údaje serveru použijeme tyto příkazy (přidáme-li k příkazům CURTIME(), CURDATE(), NOW() a SYSDATE() '+0', získáme údaj bez rozdělovacích znamének a mezer):

Čas

CURTIME()
CURRENT_TIME

mysql> select CURTIME();

 +------------+ | CURTIME()  | +------------+ | 14:05:52   | +------------+ 

nebo

mysql> select CURTIME() + 0;

 +----------------+ | CURTIME() + 0  | +----------------+ | 140552         | +----------------+ 

Datum

CURDATE()
CURRENT_DATE

mysql> select CURDATE();

 +------------+ | CURDATE()  | +------------+ | 2002-04-07 | +------------+ 

nebo

mysql> select CURDATE() + 0;

 +----------------+ | CURDATE() + 0  | +----------------+ | 20020407       | +----------------+ 

Datum a čas

NOW()
SYSDATE()
CURRENT_TIMESTAMP

mysql> select NOW();

 +-----------------------+ | NOW()                 | +-----------------------+ | 2002-04-07 14:05:52   | +-----------------------+ 

nebo

mysql> select NOW() + 0;

 +-----------------+ | NOW() + 0       | +-----------------+ | 20020407140552  | +-----------------+ 

Pokud chceme aktuální datum nebo čas vkládat do tabulek databáze uděláme to následujícím způsobem.

mysql> INSERT INTO nazev_tabulky VALUES (NOW(), ......); // obdobně použijeme CURDATE(), CURTIME() ...

Prohledávání tabulek

Pro databáze je ale jednou z hlavních funkcí vyhledávání podle zadaných parametrů. Nyní si ukážeme jak vyhledávat pomocí časových údajů. K tomuto účelu si vytvoříme jednoduchou tabulku prodeje zboží, která bude obsahovat číslo operace, název zboží a datum prodeje. Jako datum použijeme maximální, 14místný formát TIMESTAMP, který bude obsahovat rok, měsíc, den, hodiny, minuty a sekundy.

mysql> CREATE TABLE prodej(id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, nazev VARCHAR(20), datum TIMESTAMP);

PRÁCE S DATUMEM

Den v týdnu

DAYOFWEEK(datum)
Výsledek nám vrátí číselnou hodnotu dne v týdnu. Týden začíná nedělí a končí sobotou (1= neděle, 2= pondělí,.......6=pátek, 7=sobota). Tento záznam odpovídá standardu ODBC.

mysql> SELECT id,nazev FROM prodej WHERE DAYOFWEEK(datum)=2; // zobrazí čísla prodejních operací a název zboží, které se prodávalo v pondělí

WEEKDAY(datum)
Výsledek nám opět vrátí číselnou hodnotu dne v týdnu. Týden začíná v pondělí (0=pondělí, 1=úterý....5=sobota, 6=neděle).

mysql> SELECT id,nazev FROM prodej WHERE WEEKDAY(datum)=2; // zobrazí čísla prodejních operací a název zboží, které se prodávalo ve středu

DAYNAME(datum)
Výsledek vrátí anglický název dne v týdnu ('Monday', 'Tuesday'....'Saturday', 'Sunday').

mysql> SELECT id,nazev FROM prodej WHERE DAYNAME(datum)='Saturday'; // zobrazí čísla prodejních operací a název zboží, které se prodávalo v sobotu

Den v měsíci

DAYOFMONTH(datum)
Výsledek bude den v měsíci v rozmezí 1 až 31.

mysql> SELECT id,nazev FROM prodej WHERE DAYOFMONTH(datum)=20; // zobrazí čísla prodejních operací a název zboží, které se prodávalo vždy 20. den v měsíci

Den v roce

DAYOFYEAR(datum)
Výsledek bude číslo dne v roce v rozmezí 1 až 366.

mysql> SELECT id,nazev FROM prodej WHERE DAYOFYEAR(datum)=163; // zobrazí čísla prodejních operací a název zboží, které se prodalo vždy 163. den v roce

Týden v roce

WEEK(datum)
Výsledek bude číslo týdne v měsíci v rozmezí 0 až 53. Týden začíná v neděli.

mysql> SELECT id,nazev FROM prodej WHERE WEEK(datum)=13; // zobrazí čísla prodejních operací a název zboží, které se prodalo 13. týden v roce

WEEK(datum,argument)
Výsledek bude číslo týdne v měsíci v rozmezí 0 až 53. Pokud je argument 0, začíná týden v neděli. Pokud je argument 1, začíná týden v pondělí.

mysql> SELECT id,nazev FROM prodej WHERE WEEK(datum,1)=13; // zobrazí čísla prodejních operací a název zboží, které se prodalo 13. týden v roce, kdy první den týdne je pondělí

Měsíc v roce

MONTH(datum)
Výsledek bude číslo měsíce v roce v rozmezí 1 až 12

mysql> SELECT id,nazev FROM prodej WHERE MONTH(datum)=11; // zobrazí čísla prodejních operací a název zboží, které se prodávalo v listopadu

MONTHNAME(datum)
Výsledek vrátí anglický název měsíce ('January', 'February' .....'November', 'December').

mysql> SELECT id,nazev FROM prodej WHERE MONTHNAME(datum)='August'; // zobrazí čísla prodejních operací a název zboží, které se prodávalo v srpnu

Čtvrtletí

QUARTER(datum)
Výsledek nám vrátí číslo čtvrtletí v roce v rozmezí 1 až 4.

mysql> SELECT id,nazev FROM prodej WHERE QUARTER(datum)=3; // zobrazí čísla prodejních operací a název zboží, které se prodávalo od července do září

Rok

YEAR(datum)
Výsledek vrátí rok v rozmezí 1000 až 9999.

mysql> SELECT id, nazev FROM prodej WHERE YEAR(datum)=2001; // zobrazí čísla prodejních operací a název zboží, které se prodalo v roce 2001

Rok a týden

YEARWEEK(datum)
YEARWEEK(datum,argument)
Výsledek vrátí rok a týden v roce. Pro formát s argumentem platí to samé co pro příkaz WEEK().

mysql> SELECT id,nazev FROM prodej WHERE YEARWEEK(datum,1)=200123; // zobrazí čísla prodejních operací a název zboží, které se prodalo v roce 2001ve 23. týdnu, kdy týden začíná v pondělí

PRÁCE S ČASEM

Hodiny

HOUR(datum)
Výsledek vrátí hodinu v rozmezí 0 až 23.

mysql> SELECT id,nazev FROM prodej WHERE HOUR(datum)=14; // zobrazí čísla prodejních operací a název zboží, které se prodávalo mezi 14:00:00 a 14:59:59 hod

Minuty

MINUTE(datum)
Výsledek vrátí minutu v rozmezí 0 až 59.

mysql> SELECT id,nazev FROM prodej WHERE MINUTE(datum)=21; // zobrazí čísla prodejních operací a název zboží, které se prodávalo mezi 21:00 a 21:59 min

Sekundy

SECOND(datum)
Výsledek vrátí sekundu v rozmezí 0 až 59.

mysql> SELECT id,nazev FROM prodej WHERE SECOND(datum)=33; // zobrazí čísla prodejních operací a název zboží, které se prodávalo ve 33. sekundě

Požadovaný výsledek většinou získáme kombinací více dotazů. Následující příklad ukazuje, jak zjistit zboží, které se prodávalo o prázdninách roku 2001 o víkendech odpoledne. Zboží bude seřazeno podle abecedy:

mysql> SELECT nazev FROM prodej WHERE YEAR(datum)=2001 mysql> AND(MONTH(datum)=7 OR MONTH(datum)=8) mysql> AND(WEEKDAY(datum)=5 OR WEEKDAY(datum)=6) mysql> AND(HOUR(datum)>=12 AND HOUR(datum)<=17) mysql> GROUP BY nazev mysql> ORDER BY nazev;

Starší komentáře ke článku

Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.