Prakticky s MySQL 13. - práce s datem a časem

Datum a čas podruhé

V předcházející sérii článků Začínáme s MySQL se kolega Cvrček věnoval tomuto tématu sice v kontextu vestavěných funkcí MySQL také, ale dnes se podíváme na celou problematiku trošku podrobněji a ukážeme si i další možnosti z praxe.

Objasnění formátů, jenž MySQL používá k vyjádření data a času spolu s funkcemi pro aritmetické operace s nimi, je mnohdy vítaným usnadnění našich skriptů. Při dodržení rozděleného zatížení systému (s vlastním SQL serverem) u obou serverů by byla hloupost provádět složitý dotaz na SQL serveru a poté ho ještě složitě přepracovávat pomocí skriptování pokud se nám nabízí vhodné položení SQL dotazu.

Kalendářní data v MySQL

MySQL s informacemi o datu čase může pracovat různým způsobem, umožňuje přijmout takovéto údaje v různých formátech a poskytuje také následné standardizované a logické výstupy (asi by nebylo výhodné zjišťovat počet uběhlých týdnů od určitého data a dostat výstup v rocích apod.:)).

Datové typy pro práci s datem a časem mají také definovaný svůj rozsah, při jeho nenaplnění je do příslušného pole vložena nulová hodnota. Čas se tedy zobrazuje v běžné podobě v pořadí zleva hodiny minuty a sekundy. Můžeme si určit, zda data zobrazíme jako řetězec nebo čísla – to si zvolíme dle kontextu použití. Ekvivalentní jsou tedy zápisy 2002-08-15, 20020815, 2002/08/15 a 2+8+15.

Datovými typy jsme se již také zabývali, takže se je pouze stručně shrneme. Používáme zkratky z angličtiny podle nichž se lze jednoduše orientovat:

Y – year – rok

M – month – měsíc

D – day – den

H – hour – hodina

M – minute – minuta

S – second – sekunda

Datové typy data a času

Datový typ	Standardní formát	Nulová hodnota
DATETIME	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
DATE	YYYY-MM-DD	0000-00-00
TIME	HH:MM:SS	00:00:00
YEAR	YYYY	0000
TIMESTAMP	Mění se	00000000000000 nejdelší

Připomeňme si tedy pouze důležitý formát TIMESTAMP – použití je nejvýhodnější proto, že nemusíme v podstatě nic dělat - pole se aktualizuje spolu s tvorbou nového nebo aktualizací existujícího celého záznamu v tabulce – máme tu teda pole Změněno a datem poslední změny záznamu. Rozsah tohoto pole jsou hodnoty od roku 1970 do roku 2037 s přesností na jednu sekundu. Představme si tedy ještě jak mohou vypadat číslice u tohoto formátu:

Formát zobrazení datového typu TIMESTAMP

Datový typ	Formát zobrazení
TIMESTAMP(14)	YYYYMMDDHHMMSS
TIMESTAMP(12)	YYMMDDHHMMSS
TIMESTAMP(10)	YYMMDDHHMM
TIMESTAMP(8)	YYYYMMDD
TIMESTAMP(6)	YYMMDD
TIMESTAMP(4)	YYMM
TIMESTAMP(2)	YY

Pole typu TIMESTAMP jsou tedy aktualizovány námi vyžádanou formou aktuálního času – ovšem pozor, pouze v případě, že do nich nic nezapisujeme. Obdobný efekt nám pak nabízí funkce NOW(). Všechna pole TIMESTAMP obsazují 4 bajty paměti. Můžeme je upravovat příkazem ALTER TABLE a přitom o uložená data nepřijdeme – navíc při rozšíření pole se nám objeví v tabulce i údaje, které byly předtím skryty a taktéž při zúžení pole o data nepřicházíme.

Pozor na konverze datových typů

Než se vrhneme na praxi s vnitřními funkcemi pro práci s datem systému MySQL, řekneme si ještě pravidla pro převod data mezi různými datovými typy. Vycházejme z pole typu DATETIME – zde si uložíme přesné datum vložení našeho produktu do databáze. Nyní ovšem chceme tyto údaje převést do další tabulky a bude nám zde vyhovovat uvedení pouze datum vložení. Převedeme si tedy pole DATETIME na DATE – nyní již informace u přesném času ztratíme, protože na ty datový typ DATE nemá kapacitu. Stejně tak se nám při opačném převodu nikde „neobjeví“ údaj o přesném času. Navíc pokud by hodnota DATE nebyla v intervalu TIMESTAMP – třeba rok 2038 – bude datové pole navíc vynulováno.

Vnitřní funkce systému MYSQL pro práci s datem

Při práci s datem a časem v systému MySQL využíváme ve všech příkazech SELECT u klauzulí WHERE. Začneme tedy rovnou příkladem z praxe, chceme vybrat všechny záznamy, kde je měsíc v rámci záznamu TIMESTAMP v poli Zmeneno tabulky Priklad ten námi zvolený – použijeme jeho jméno (anglické) – aktuální by byly srpnové záznamy:

Vstup:

SELECT Zmeneno FROM Priklad WHERE MONTHNAME(Zmeneno)=“August“

Výstup:

Zmeneno

20020804

20020821

20020827

Zobrazení dne

Pro převod data na číslo dne slouží několik jednoduchých funkcí – DAYOFYEAR, DAYOFMONTH, DAYOFWEEK, WEEKDAY – vždy s argumentem datum. Tyto funkce by nám již měly být známé a proto si pouze ukážeme jedno (ne)praktické použití:):

Vstup:

SELECT WEEKDAY(“2-8-3”)

Výstup:

5

Jak vidíte, příkaz může mít i poměrně nepraktický vstup i výstup, ale vše se odvíjí od našich potřeb. Chceme-li získat název měsíce nebo dne, slouží nám k tomu funkce MONTHNAME a DAYNAME opět s parametry datum, generují anglické jména. Nemůžeme ovšem používat neplatným argument datum jako například toto:

Vstup:

SELECT MONTHNAME(“8”)

Výstup:

NULL

Zobrazení roku, čtvrtletí, měsíce a týdne

Pro další kalendářní údaje nám slouží funkce YEAR (datum), QUARTER (datum), MONTH (datum), WEEK (datum, prvniden), YEARWEEK (datum, prvniden). Pokud neurčíme u funkce WEEK druhý argument, bude považována neděle za první den týdne. Nyní si ukážeme dva odlišné výstupy:

Vstup:

SELECT WEEK(“2002-08-11”)

Výstup:

33

Vstup:

SELECT WEEK(“2002-08-11”, 1)

Výstup:

32

Obdobně funguje i funkce YEARWEEK s tím, že vrací kombinaci roku a týdne:

Vstup:

SELECT WEEK(“2002-08-11”, 1)

Výstup:

200232

Aktuální datum a čas

I zde existuje řada funkcí – NOW(), SYSDATE(), CURRENT_TIMESTAMP, CURDATE(), CURRENT_DATE, CURTIME(), CURRENT_TIME. Samozřejmě je nejvýhodnější funkce NOW() kdy rozdíl použití a nepoužití číselného výsledku vypadá takto:

Vstup:

SELECT NOW()

Výstup:

2002-08-15 11:11:54

Vstup:

SELECT NOW()+0

Výstup:

20020815111154

U funkcí SYSDATE() a CURRENT_TIMESTAMP je výsledek stejný jako u předešlé funkce NOW() podle použití číselného kontextu, další 2 funkce vrací pouze aktuální čas.

Formátování data a času

I zde se omezím na stručný přehled, i když jsou zmíněné funkce díky své variabilitě dosti důležité – DATE_FORMAT(datum, formát) a TIME_FORMAT(datum, formát), i tímto tématem jsme se již letmo zabývali a proto pouze připojím kombinované užití:

Vstup:

SELECT TIME_FORMAT(NOW(), “%H:%i:%s”)

Výstup:

19:12:42

Hodina, minuta, sekunda

Pracujeme zde s funkcemi HOUR(čas), MINUTE(čas), SECOND(čas). Kombinujeme je s výše uvedenými funkcemi a tím dosahujeme třeba tohoto:

Vstup:

SELECT HOUR(NOW())

Výstup:

19

Počet dnů po Kristu

Zde si ukážeme zcela nové a „velmi praktické“:) funkce pokud jde o náš minulý přehled – TO_DAYS(datum) a FROM_DAYS(číslo). U funkce TO_DAYS vkládáme datum a výsledkem je počet dnů, které uběhly od prvního dne našeho letopočtu:

Vstup:

SELECT TO_DAYS(NOW())

Výstup:

731442

Obráceným postupem bychom mohli zjistit, kdy bude miliontý den po Kristu:

Vstup:

SELECT TO_DAYS(1000000)

Výstup:

2737-11-28

Sekundy od půlnoci

Zde se nám objevují funkce TIME_TO_SEC(čas) a SEC_TO_TIME(sekundy). První jmenovaná funkce vrací počet sekund od půlnoci:

Vstup:

SELECT TIME_TO_SEC(01.00.00)

Výstup:

3600

Ovšem použijeme-li hodnotu 110, vrátí se nám číslo 70 – číselnou hodnotu 110 MySQL reprezentuje jako 1 minutu a 10 sekund. Funkce SEC_TO_TIME je zcela opačná a převádí počet sekund od půlnoci na čas ve formátu HH:MM:SS či HHMMSS.

Vstup:

SELECT SEC_TO_TIME(1000)+0

Výstup:

1640

Čas a UNIX

Naopak hojně využívané funkce jsou UNIX_TIMESTAMP(datum) a FROM_UNIXTIME(časová_značka_UNIX, formát). Časová_značka_UNIX vrací počet sekund od 1. ledna 1970. Funkce UNIX_TIMESTAMP bez argumentu vrací aktuální čas systému UNIX jako třeba:

Vstup:

SELECT UNIX_TIMESTAMP()

Výstup:

949882147

Při argumentu datum funkce UNIX_TIMESTAMP vrací funkce čas určeného data:

Vstup:

SELECT UNIX_TIMESTAMP(“2005-03-10 11:11:11”)

Výstup:

1110449471

Funkcí FROM_UNIXTIME() získáme datum a čas – jako text či číslo:

Vstup:

SELECT FROM_UNIXTIME(949872354)*1

Výstup:

20000206212554

Vstup:

SELECT FROM_UNIXTIME(1000000000, “%W, %M”)

Výstup:

Saturday, September

Sčítání a odčítání času

Tyto funkce pro sčítání a odčítání času MYSQL obsahuje od verze 3.22 a dále se rozšiřují. Výstup v nich lze formátovat podle přiložené tabulky a od verze 3.23 je možné také používat operátory + a – namísto DATE_ADD() a DATE_SUB().

Povolené hodnoty pro výraz a typ při sčítání a odčítání času

Hodnota argumentu typ	Očekávaný formát výrazu
SECOND	SEKUNDY
MINUTE	MINUTY
HOUR	HODINY
DAY	DNY
MONTH	MĚSÍCE
YEAR	ROKY
MINUTE_SECOND	“MINUTY:SEKUNDY”
HOUR_MINUTE	“HODINY:MINUTY”
DAY_HOUR	“DNY HODINY”
YEAR_MONTH	“ROKY-MĚSÍCE”
HOUR_SECOND	“HODINY:MINUTY:SEKUNDY”
DAY_MINUTE	“DNY HODINY:MINUTY”
DAY_SECOND	“DNY HODINY:MINUTY:SEKUNDY”

Funkce pro sčítání a odčítání času

DATE_ADD(datum, INTERVAL výraz typ)

ADDDATE(datum, INTERVAL výraz typ)

DATE_SUB(datum, INTERVAL výraz typ)

SUBDATE(datum, INTERVAL výraz typ)

PERIOD_ADD(měsíce, počet)

PERIOD_DIFF(měsíc1, měsíc2)

Nebudeme tedy jednotlivé funkce již popisovat jinak než jejich praktickým užitím:

Přidání intervalu:

Vstup:

SELECT DATE_ADD(“1980-02-01”, INTERVAL 20 YEAR)

Výstup:

2000-02-01

Vstup:

SELECT DATE_ADD(“02-02-01 09:00:00”, INTERVAL 36 HOUR)

Výstup:

2002-02-02 21:00:00

Odečtení intervalu:

Vstup:

SELECT DATE_SUB(NOW(), INTERVAL 3 DAY)

Výstup:

2002-08-14 08:21:15

Přidání či odečtení měsíců od tvaru YYMM nebo YYYYMM:

Vstup:

SELECT PERIOD_ADD(0806,12)

Výstup:

200906

Vstup:

SELECT PERIOD_ADD(0806,-2)

Výstup:

200804

Rozdíl mezi dvěma měsíci:

Vstup:

SELECT PERIOD_DIFF(200006,198001)

Výstup:

245

A jak dál?…

Dnes jsme si ukázali něco z praxe práce s daty a časem v databázích MySQL a příště se podíváme, jak lze databáze spravovat pomocí programu mysqladmin.