Ukládání datumů v databázi

Školení, která pořádám

Ve většině databází je k dispozici speciální typ pro ukládání datumu kalendářního data – obvykle se jmenuje DATE. Já ho mám rád a používám ho, někdo ale tvrdošíjně pro ukládání datumu používá obyčejné číslo, obvykle ve významu UNIX_TIMESTAMP a argumentuje tím, že se s ním pohodlněji pracuje. To jednak není pravda a jednak je chyba nevyužívat existující infrastrukturu – kvůli tomu vznikl problém roku 2000 a kvůli tomu bude problém i s rokem 2038 (kdy 32bitový timestamp přeteče).

Zdánlivá vyšší jednoduchost práce s datumem uloženým v čísle je při jeho formátování:

<?php
$row = mysql_fetch_assoc(mysql_query("SELECT * FROM diskuse"));

// datum uložený v číselném sloupci
echo date("j.n.Y H:i:s", $row["vlozeno"]);

// datum uložený ve sloupci typu DATE
echo preg_replace('~^([0-9]+)-0?([0-9]+)-0?([0-9]+)~', '\\3.\\2.\\1', $row["vlozeno"]);

// mezipřevod na timestamp v PHP
echo date("j.n.Y H:i:s", strtotime($row["vlozeno"]));

// formátování na úrovni databáze
$row = mysql_fetch_assoc(mysql_query("SELECT *, DATE_FORMAT(vlozeno, '%e.%c.%Y %H:%i:%s') AS vlozeno_f FROM diskuse"));
echo $row["vlozeno_f"];
?>

Vzhledem k tomu, že všude na webu budeme chtít datum nejspíš vypisovat jednotně, je stejně lepší si na formátování datumu napsat funkci a všude ji používat – co bude uvnitř této funkce je druhořadé, volání bude stejně snadné v obou případech. Datum se dá funkcí DATE_FORMAT formátovat už na úrovni databáze, to ale při používání mysql_fetch_assoc práci poněkud znepohodlňuje (je třeba aliasovat).

Při uložení datumu do správného typu je také s datumem čistší práce na úrovni databáze:

<?php
// smazání diskusních příspěvků starších než 14 dní
mysql_query("DELETE FROM diskuse WHERE vlozeno + INTERVAL 14 DAY < NOW()"); // datum uložený ve sloupci typu DATE
mysql_query("DELETE FROM diskuse WHERE vlozeno + 14*24*60*60 < UNIX_TIMESTAMP()"); // datum uložený v číselném sloupci
?>

Ke sloupci typu DATE není vhodné přičítat číslo, protože v tom případě ho MySQL převede na formát YYYYMMDD, takže např. přičtení 100 přičte jeden měsíc.

Přijďte si o tomto tématu popovídat na školení Návrh a používání MySQL databáze.

Jakub Vrána, Výuka, 17.10.2005, diskuse: 28 (nové: 0)

Diskuse

Michal Hantl:

Proč by neměl být timestamp za 33 let třeba 128 bitový? To bych se vsadil, že budou určitě minimálně 128bitové procáky.

Llaik:

Mozna jsem naivni, ale take se mi nechce verit, ze bychom meli mit za vice nez 30 let problem s nejakymi 32 bity :)

btw - je vam vsem jasne, ze klasicke datum bude mit setsakra problem v roce 10000? :)

ikona Jakub Vrána OpenID:

Problém je podobný jako s rokem 2000. Vznikl proto, že lidem bylo líto zapisovat rok čtyřciferně. Problém roku 2038 bude v tom, že lidem je líto zapisovat timestamp nějakým zvláštním typem. Používá se čtyřbajtový int a ještě nějakou dobu se používat bude. Před rokem 2038 se jistě používat přestane (třeba už za nějakých 10 let s kompletním nástupem 64bitových procesorů), ale nutnost zkontrolovat, jestli pro uložení data a času někde není použit čtyřbajtový int, zůstane. Stejně, jako u problému roku 2000 nestačilo před všechny datumy dopsat "19".

<em>anonymní</em>:

Misto ctyrbytoveho intu se proste zacne pouzivat osmibytovy.

ikona Jakub Vrána OpenID:

Jistě, ale co staré aplikace? Vyměnit všechny čtyřbajtové inty za osmibajtové by mohlo napáchat ještě větší škody.

ikona llook:

No právě. Kdyby všichni používali time_t, tak by se předefinovala jedna konstanta v jednom hlavičkovém souboru, všechno by se překompilovalo a hotovo. Ale co když někdo někde neprozřetelně použil přímo int, nebo dokonce int32?

ikona llook:

Počítače nejsou jenom osobní počítače. Myslíš že se už dneska nepoužívají osmibitové a šestnáctibitové procesory?

Michal Hantl:

Šlo mi hlavně o to, že argument toho, že za 33 let timestamp "dojde" asi nebude hlavním důvodem, proč ukládat čas v jiném formátu. Navíc.. za 33 let bude php verze XY a problém se bude řešit jinak. Teda.. jestli v php ještě nějaký exot bude dělat:).

ikona llook:

S tím souhlasím. Zrovna v MySQL to lze vyřešit už teď použitím BIGINT místo INT.

Sranda může být spíše s některými jednoúčelovými počítači. Ale pádů letadel a výpadků elektřiny se bát nemusíme, taková sranda to asi nebude. :-)

ikona Jakuje:

Co máš za problém s php a co máš proti nám exotům co v něm děláme?

trpaslice.helga:

Já myslel, že to nemyslel jako urážku dnešních php kodérů...jen prostě za 33 let asi těžko bude php ještě používané...před x lety si taky lidi mysleli, že některé programovací jazyky vydrží navždy jak jsou dokonalé...a hle, po některých není ani vidu, ani slechu už desítky let...

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL: Reakce na: trpaslice.helga

ia:

Tiež by som upozornil na MySQL funkciu UNIX_TIMESTAMP(), ktorá vrati priamo timestamp aj zo stlpca typu DATE alebo DATETIME:

SELECT UNIX_TIMESTAMP( `vlozeno` ) FROM ...

http://dev.mysql.com/doc/refman/5.0/en/dat…-functions.html

salko:

A ja by som pridal, ze typ DATE je vynikajuci, ale vo verziach MySQL 3.x (neviem ako su na tom 4 verzie) uklada data vyhradne podla lokalneho casoveho pasma servera. Inak povedane, nema ulozene nikde udaj o tom, ze v akom casovom pasme sa dana hodnota nachadza. Ale ludia co nepotrebuju pracovat s casovymi pasmami sa tato vec netyka.

Lukáš Mačí:

Malinko OT:Nevím, jestli je zrovna vhodné považovat za nevýhodu jednoho řešení a výhodu druhého něco, co nastane v tak daleké budoucnosti. Jsme si jistí, že v 2038 budeme používat databáze tak jako dnes?

Jan Vrana:

Problem není pouze v budoucnostu ale i v minulosti.
Skuste si naprogramovat napriklad databazy historickych udalosti a pokud mozno s polem DATUM pracovat jako s Datumem. Myslim ze uz na zacatku 20stoleti zacnou problemy. :((

ikona llook:

Problém roku 2038 se mi zdá možná i závažnější než 2000. Nevím kolik lidí by napadlo šetřit binární pamětí tím, že se použije méně dekadických cifer.
Zatímco ten signed int je vidět na hodně místech, například v 32-bitovém Linuxu. Taky nechápu, proč se používá signed, když unsigned by vydrželo dvakrát dýl...

Ale k tématu. Teď se začíná docela prosazovat SQLite. SQLite 2 pracuje se vším jako s řetězcem, nepracuje se tam lépe s timestampem než s ISO datem?

Jakub Podhorský:

dřív jsem taky používal zásadně unix timestamp ale potom co jsem se naučil s funkcema pro práci s datumem v MySQL jsem ho naprosto zavrhl...navíc už na první pohled přesně vím co ten datum znamená narozdíl od směsice čísel

Leo:

Ono asi hodne zalezi na tom, k cemu to datum/cas v db pouzivate (jinak by stacil jediny zpusob zapisu).  Pokud to ma byt lidsky citelne (delam z toho treba vystup na stranku, nebo co db koukam casto vlastnima ocima primo), pak treba DATETIME, pokud to slouzi treba jen k hlidani timeoutu, staci tak ukladat time z PHP. Leo

halogan:

Jen FYI:
http://pravidla.cz/hledej.php?qr=datum%F9

RejpalCZ:

Hádám, že tvar "datumů" byl použit nikoli z neznalosti, nýbrž kvůli možným problémům s výkladem správného tvaru.
Přeci jen, co si představíte, když se řekne "Ukládání dat v databázi?"
... tohle je sice gramaticky špatně, ale zato všem hned jasné :-)

ikona Jakub Vrána OpenID:

Hádáš správně ;-).

Medhi:

Posílal jsem již mail, ale radši než neexistující slovo bych použil třeba kalendářních dat.

ikona Jakub Vrána OpenID:

„U podstatného jména datum nelze odmítat v některých typech textů (zvláště těch, v nichž je třeba rozlišit kalendářní a počítačová data) ani tvary mužského rodu zejména v množném čísle, tj. datumy, datumů, datumům, datumy, o datumech, datumy. Např. Mezi nejčastější data patří datumy.“ http://prirucka.ujc.cas.cz/?id=263#nadpis3

Arcao:

Hlavne se mi zda ze porovnavani casu s unix timestampem je rychlejsi. A o to tady vetsinou jde.

Jakub Podhorský:

imho s dnešní výpočetní silou? má se o tom vůbec cenu bavit a vůbec se takovou zbytečnostní zahazovat....pro mě osobně je důležitější že když například při vývoji často testuju aplikaci a do DB ukládám datumy a potřebuju vědět co se mi tam přesně ukládá tak radši mám tabulku ve formátu DATE ze které hned na první pohled vím o co de a ne tu směsici čísel z který vím akorát tak jednu věc :)

Antonín:

No. S dnešní výpočetní silou... Ok můžeš tomu říkat zbytečnost, když se jedná o pár operací. Představ si, že budeš potřebovat pracovat cca s 5 000 000 údaji v databázi a s každým z nich provést nějakou operaci. V tomhle případě tě již začne zajímat, který ze způsobů je rychlejší.

<em>anonymní</em>:

Tohle jsem potreboval vedet, dekuju !

Jirka Táborský:

Po mnoha letech jsem nasel DATE_FORMAT, zaplat buh za nej, dost mi to zjednodusuje praci.
avatar © 2005-2019 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.