Tvorba databází v MySQL - VI

25. 4. 2003 | David Hauzar

Články - Tvorba databází v MySQL - VI  

Poznáte datové typy určené k uchovávání data a času a většinu funkcí určených pro práci s datem a časem.

Zobrazení a zadávání data a času v MySQL

V MySQL se zcela vlevo zadávají nejméně podrobné informace (např. rok) a zcela vpravo nejvíce podrobné informace (např. sekundy).

MySQL je při přijímání kalendářních dat velmi pružný. Následující datumy jsou z pohledu MySQL stejné:

"2002-12-27" = "2002/12/27" = "2+12+27" = 20021227 ="2002+12+27"

Můžete tedy datum zadávat jak v číselném formátu (20011227), tak jako řetězec (např. "2002+12+27").

MySQL načítá informace o datu/čase zprava. Například pokud zadáte do sloupce s datovým typem TIME (který má formát zápisu: HH:MM:SS) hodnotu 9:50, systém MySQL ji přeloží jako 00:09:50 - ne jako 09:50:00

Počet dnů musí být z intervalu <1; 31>, počet měsíců z intervalu <1; 12>.

Datové typy data a času

V MySQL se pro ukládání data a času používají speciální datové typy.

DATETIME

DATETIME uchovává úplnou informaci o datu a čase s přesností na 1 sekundu. Standardní formát zápisu je tedy tento:

YYYY-MM-DD HH:MM:SS

Hodnoty zadávané do sloupce s datovým typem DATETIME mohou být z intervalu:

<1000-01-01 00:00:00; 9999-12-31 23:59:59>

DATE

DATE uchovává pouze datum bez informace o čase. Standardní formát zápisu je tedy tento:

YYYY-MM-DD

Hodnoty zadávané do sloupce s datovým typem DATE mohou být z intervalu:

<1000-01-01; 9999-12-31>

TIME

TIME uchovává pouze informaci o čase. Standardní formát zápisu je tedy tento:

HH:MM:SS

Hodnoty zadávané do sloupce s datovým typem TIME mohou být z intervalu:

<-838:59:59; 838:59:59>

YEAR

YEAR uchovává informace o kalendářním roku. Standardní formát zápisu je tedy tento:

YYYY

Hodnoty zadávané do sloupce s datovým typem YEAR mohou být z intervalu:

<1901; 2155>

Pokud budete zadávat rok ve dvoumístném formátu, budou hodnoty z intervalu <0; 69> převedeny na hodnoty z intervalu <2000; 2069> a hodnoty z intervalu <70; 99> budou převedeny na hodnoty z intervalu <1970; 1999>.

TIMESTAMP

Pokud určíte datový typ sloupce jako TIMESTAMP, bude se vám při každém vložení nového záznamu nebo aktualizaci existujícího záznamu tento sloupec automaticky plnit hodnotou aktuálního data a času.

Vše co musíte udělat, je vložit do takového sloupce prázdnou hodnotu. Pokud máte v tabulce pouze jeden sloupec typu TIMESTAMP, nemusíte dokonce do sloupce prázdnou hodnotu vkládat, pokud máte v tabulce více sloupců typu TIMESTAMP a do žádného nevložíte prázdnou hodnotu, vloží se vám aktuální čas a datum jen do prvního z nich.

Když do sloupce typu TIMESTAMP vložíte něco jiného, než prázdnou hodnotu, aktuální datum a čas se do něj neuloží!

Datový typ TIMESTAMP uchovává hodnoty vždy v intervalu od roku 1970 do roku 2037 s přesností na sekundy. Můžete ale určit, v jakém formátu budou data zobrazena. Pokud nastavíte TIMESTAMP(2) a necháte si tak zobrazovat pouze informace o roce, do daného sloupce se budou dále ukládat i informace o celém datu i s časem, pouze se nebudou zobrazovat. Pokud si v budoucnu tento sloupec upravíte na TIMESTAMP(14) a necháte si zobrazit dříve uložená data, zobrazí vám MySQL data v příslušné přesnosti.

Formáty zobrazení datového typu TIMESTAMP

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

Konverze mezi datovými typy

MySQL umožňuje převod mezi datovými typy. Přebytečná data budou odříznuta a nenávratně ztracena, naopak chybějící data budou nahrazena nulovou hodnotou.

Problém nastává, pokud převáděné datum přesahuje interval datového typu, do kterého má být převedeno. Data pak budou znehodnocena.

Funkce MySQL pro práci s datem

MySQL poskytuje velké množství funkcí pro práci s datumem a časem.

Funkce, vracející datum/čas vracejí datum/čas implicitně jako řetězec (např. "2001-12-26"). Pokud chcete, aby funkce vracely datum/čas jako číslo (20011226) musíte za příslušnou funkci zadat +0 (např. NOW()+0).

Převod data na číslo dne

• DAYOFYEAR(datum)

  vrací den roku na základě data datum (spočítá, kolik dní uplynulo od 1. ledna)

• DAYOFMONTH(datum)

  vrací den v měsíci na základě data datum

• DAYOFWEEK(datum)

  vrací den v týdnu na základě data datum

• WEEKDAY(datum)

  vrací index dne v týdnu (0 = pondělí, 1 = úterý, ...)

Dotaz:

SELECT DAYOFYEAR(20020201);

vrátí: 32

Názvy měsíců a dnů

• MONTHNAME(datum)

  zobrazí anglický název měsíce, v němž se datum datum nachází

• DAYNAME(datum)

  zobrazí anglický název dne, který určuje datum datum

Dotaz:

SELECT DAYNAME("2003-01-06");

vrátí: Monday

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

• YEAR(datum)

  vrací rok na základě data datum

• QUARTER(datum)

  vrací čtvrtletí (číslo z intervalu <1, 4>) na základě data datum

• MONTH(datum)

  vrací měsíc (číslo z intervalu <1, 12>) na základě data datum

• WEEK(datum[ , první_den])

  Vrací číslo týdne v roce. Jako první den týdne je ale implicitně považována neděle. Pokud chcete, aby funkce pracovala správně v českých podmínkách, musíte druhý, nepovinný argument první_den nastavit na index pondělí (index neděle = 0, index pondělí = 1, ...)

• YEARWEEK(datum[ , první_den])

  funguje stejně jako funkce WEEK(), výstup ale obsahuje i informaci o roku

Dotaz:

SELECT WEEK("2003-01-05",1);

vrátí: 1

Aktuální datum a čas

• NOW() = SYSDATE() = CURRENT_TIMESTAMP

  tyto funkce vracejí aktuální čas a datum ve formátu DATETIME

• CURDATE() = CURRENT_DATE

  používají se stejně, ale vrací pouze datum

• CURTIME() = CURRENT_TIME

  zjišťují pouze čas

Formátování datumu a času

• DATE_FORMAT(datum, formát)
• TIME_FORMAT(čas, formát)

  tyto funkce umožňují zobrazit datum/čas v požadovaném formátu

Formátování datumu a času

Argument formát	Výstupní formát
%r	12 hodinový formát času (hh:mm:ss (AM/PM)).
%T	24 hodinový formát času (hh:mm:ss)
%Y	Číselné vyjádření roku, čtyřmístné.
%y	Číselné vyjádření roku, dvoumístné.
%m	Měsíc s vedoucí nulou (01, 02..12).
%c	Měsíc bez vedoucí nuly (1, 2..12).
%M	Anglický název měsíce (January, February atd.).
%b	Anglický název měsíce zkrácený (Jan, Feb atd.).
%D	Den v měsíci jako anglická řadová číslovka (1st, 2nd, atd.).
%d	Den v měsíci s vedoucí nulou (00, 01, 02...31).
%e	Den v měsíci bez vedoucí nuly (0, 1, 2...31).
%W	Anglický název dne v týdnu (Sunday, Monday atd.).
%a	Anglický název dne v týdnu, zkrácený (Sun, Mon atd.).
%H	Hodina (00, 01...23).
%k	Hodina (0, 1...23).
%h	Hodina (01, 02...12).
%I	Hodina (01, 02...12).
%l	Hodina (1, 2...12).
%i	Minuty (00, 01...59).
%S	Sekundy (00, 01...59).
%s	Sekundy (0, 1...59).
%P	AM nebo PM
%U	Číslo týdne v roce, když je neděle prvním dnem v týdnu.
%u	Číslo týdne v roce, když je pondělí prvním dnem v týdnu.
%X&%V	Rok a číslo týdne v roce, když je neděle prvním dnem v týdnu.
%x&%v	Rok a číslo týdne v roce, když je pondělí prvním dnem v týdnu.
%j	Den v roce s vedoucími nulami (001, 002...366).
%w	Číslo dne v týdnu (0=neděle, 1=pondělí, 2=úterý, ...).
%%	Literál %.

Dotaz:

SELECT DATE_FORMAT("2003-11-28 21:00:00", "%r on %W");

vrátí: 09:00:00 PM on Friday

Dotaz:

SELECT DATE_FORMAT (NOW(), "%H:%i:%s");

vrátí například: 13:33:36

Informace o hodině, minutách a sekundách

• HOUR(čas)

  vrátí číslo hodiny na základě času čas. Číslo bude z intervalu <0; 23>

• MINUTE(čas)

  vrátí číslo minuty na základě času čas. Číslo bude z intervalu <0; 59>

• SECOND(čas)

  vrátí číslo sekundy na základě času čas. Číslo bude z intervalu <0; 59>

Počet dnů našeho letopočtu

• TO_DAYS(datum)

  vrací počet dnů, které uplynuly od prvního dne našeho letopočtu

• FROM_DAYS(číslo)

  vrací datum našeho letopočtu na základě počtu dnů, které uplynuly od počátku letopočtu

Počet sekund od půlnoci

• TIME_TO_SEC(čas)

  vrací počet sekund od půlnoci na základě času čas

• SEC_TO_TIME(sekundy)

  převádí počet sekund od půlnoci na čas

Čas v systému UNIX

• UNIX_TIMESTAMP([datum])
  • bez argumentu datum vrací počet sekund od 1. 1. 1970 (časovou značku UNIX)
  • s argumentem datum (datum ve formátu DATE, DATETIME, TIMESTAMP, číslo ve tvaru YYMMDD nebo YYYYMMDD) vrací počet sekund od 1. 1. 1970 do data datum
• FROM_UNIXTIME(časová_značka_UNIX [, formát])
  • bez argumentu formát, vrátí na základě počtu sekund od 1. 1. 1970 datum a čas ve formátu YYYY-MM-DD HH:MM:SS.
  • pomocí argumentu formát můžete formát zobrazeného data. Lze použít všech formátovacích značek funkce FORMAT_DATE()

Dotaz:

FROM_UNIXTIME(1000000000, "%W, %M);

vrátí: Saturday, September

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

• DATE_ADD(datum , INTERVAL výraz typ) = ADDATE(datum , INTERVAL výraz typ)

  přičítání

• DATE_SUB(datum , INTERVAL výraz typ) = SUBDATE(datum , INTERVAL výraz typ)

  odečítání

Funkce očekávají argument datum, k němuž přičtou/odečtou časový interval určený výrazem výraz. Typ výrazu určuje argument typ

Místo těchto funkcí můžete použít i operátory + a -

Argument typ může nabývat těchto hodnot:

Možné hodnoty argumentu typ

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"

Následující dotaz přidá 18 let k 27. prosinci 1984:

SELECT DATE_ADD("1980-12-27", INTERVAL 18 YEAR);

výstup: 2003-12-27

Následující dotaz odečte od 21 hodin 25. 11. 2003 50 dní a 27 hodin:

SELECT DATE_SUB("2003-11-25 21:00:00", INTERVAL "50 27" DAY_HOUR);

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

  očekává měsíc ve formátu YYMM nebo YYYYMM, k němuž přidá počet dalších měsíců (pokud budete chtít měsíce odebrat, zadejte záporný argument počet

Dotaz:

SELECT PERIOD_ADD(0806,12);

vrátí: 200906

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

  zjistí počet měsíců mezi měsíci měsíc1 a měsíc2

• EXTRACT(typ FROM datum)

  Tato funkce se používá k oddělení a zobrazení dílčích složek data a času z datumu datum. Zobrazí se pouze část datumu specifikovaná v argumentu typ, krerý může obsahovat stejné hodnoty, jako argument typ u funkce DATE_ADD()

Závěr

Datum můžete v MySQL zadávat jako řetězec i jako číslo. MySQL má pro uchovávání datumu a času zvláštní datové typy a poskytuje velké množství funkcí pro práci s datumem a časem.

Tento díl zakončil část seriálu věnovanou vlastní tvorbě databází v MySQL. Od příštího dílu se budete učit databázi MySQL spravovat. V příštím dílu se naučíte databázi správně zabezpečit.

Seriál MySQL - tvorba databází (dílů: 6)

První díl: Tvorba databází v MySQL - I, poslední díl: Tvorba databází v MySQL - VI.
Předchozí díl: Tvorba databází v MySQL - V