Práce s datumy v MySQL a výstupem z PHP

Jaký zvolit datový sloupec? A jak jednoduše získat formátovaný výstup z unixového časového razítka popřípadě z datumu ve formátu DATETIME?

Teď trochu obecně o jednotlivých datových typech, i když je to nuda – doporučuji přečíst:-D.

Ukládání času do mysql databáze lze udělat více způsoby a v různých formátech.
Může jít o datové sloupce:

  • TIMESTAMP
  • DATE
  • TIME
  • DATETIME
  • YEAR
  • INT
  • Nebo jen čistě uložený řetězec (CHAR, VARCHAR, TEXT..) v databázi, nedoporučuji.

TIMESTAMP

Timestamp je „datumočas“ ve formátu „RRRRMMDDHHMMSS“ a rozsahu od 1970–01–01 00:00:00 do 2037–01–01 00:00:00.

Datový typ TIMESTAMP je výhodný pro ukládání datumu vložení řádku (výchozí hodnota je CURRENT_TIMESTAMP), protože při úpravě se aktualizuje i čas v upraveném řádku. (Vlastnost ON UPDATE CURRENT_TIMESTAMP).

V databázi zabírá 4 bajty, při výstupu lze formát datumu a času libovolně měnit (funkce DATE_FORMAT()).

DATE

Uloží datum ve formátu „YYYY-MM-DD“ a s rozsahem „1000–01–01“ až „9999–12–31“.
Zabírá 3 bajty a taktéž lze při výstupu jednoduše formátovat.

TIME

Čas ve formátu „hh:mm:ss“, většinou prezentuje čas dne, ale může to být i uplynulý čas. Proto má celkem neobvyklý rozsah „-838:59.59“ až „838:59:59“.
Taktéž 3 bajty a jednoduché formátování.

DATETIME

Datum a čas ve formátu „YYYY-MM-DD hh:mm:ss“, rozsah „1000–01–01 00:00:00“ do „9999–12–31 23:59:59“.
Zabírá 8 bajtů.

YEAR

Rok YYYY, rozsah 1901 až 2155 a zabírá 1 bajt.

INT

INT je číselný typ a nikoliv datumový, přesto se v něm dá datum snadno uložit a i jednoduše s tím dá pracovat.

Informace o čase lze uložit i jako číslo, které prezentuje počet uplynulých sekund od začátku unixového času (1.1.1970) – takzvaný UNIX TIMESTAMP. V PHP vrací funkce time() právě počet sekund od začátku unixového času. Jako datový typ pro UNIXové časové razítko doporučuji INT(10). Zabírá 4 bajty.

Ostatní datové sloupce se pro ukládání času nehodí.

Datové typy je dobré znát, usnadní vám to spoustu práce a ušetří i nějaké to místo v databázi. Což sice není v dnešní době příliš podstatné, ale hlavně nedochází ke zbytečným ztrátám výkonu aplikací. (Jen pro představu – uložíte-li si datum v libovolném formátu do datového typu VARCHAR(255), bude zabírat místo 4–8 bajtů celých 256!)

Od teorie se odprostím, ukážu pár příkladů, které se budou hodit.

Vytvoříme si testovací tabulku ve které budeme mít datum uložený ve 3 různých datových formátech, např:

CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `jmeno` VARCHAR( 64 ) NOT NULL ,
  `heslo` CHAR( 32 ) NOT NULL ,
  `cas` DATETIME NOT NULL ,
  `cas_zmeny` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `cas_unix` INT( 10 ) NOT NULL
);

Vše lze „naklikat“ v phpmyadminovi nebo tento kód okopírujte a vložte v phpmyadminu do „SQL“. Takže tabulku máme, teď se půjdeme podívat, jak zobrazovat, vkládat či upravovat data z PHP skriptů.

Vložení a úprava:

Vezmu jen útržek skriptu s SQL dotazem, jak získat třeba data z formuláře je popsáno v tomto seriálu registrace.

Příklad 1:

<?php
/****** Přidání
* Připojení na databázi, získání proměnných, jejich ošetření atd...
*/
$Vlozeni=mysql_query("
          INSERT INTO `test`
              (`jmeno`,`heslo`,`cas`, `cas_unix`)
          VALUES
             ('".$Jmeno."', '".$Heslo."', NOW(), ".time().")
          ") or die ("Chyba v SQL dotazu: " . mysql_error());

// Naschvál jsem vynechal sloupec `cas_zmeny`, vloží se automaticky
// (Výchozí DEFAULT CURRENT_TIMESTAMP)

if($Vlozeni){
  echo "Záznam byl uložen";
}
?>

<?php
/****** Úprava
* To samé i při úpravě (spojení na databázi, získání proměnných a jejich ošetření)
*/

$Uprava=mysql_query("
          UPDATE `test`
              SET
              `jmeno`='".$Jmeno."',
              `heslo`='".$Heslo."'
            WHERE
              `id`=".(int)$_POST['id_opravovaneho_zaznamu']."
          ") or die ("Chyba v SQL dotazu: " . mysql_error());

// Naschvál jsem vynechal sloupec `cas_zmeny`, upraví se automaticky
// (Vlastnost ON UPDATE CURRENT_TIMESTAMP)

if($Uprava){
  echo "Záznam byl upraven";
}
?>

Výpis do tabulky je pak jen otázkou chviličky.

Příklad 2:

<?php
/*
* Připojení na databázi...
*/
$Dotaz=mysql_query("
      SELECT
         `id`, `jmeno`, `cas` , `cas_zmeny`, `cas_unix`
      FROM `test`
      ") or die ("Chyba v SQL dotazu: " . mysql_error());

echo "<table>";
while($zaznam=mysql_fetch_array($Dotaz)){
  echo "
    <tr>
      <td>".$zaznam['id']."</td>
      <td>".$zaznam['jmeno']."</td>
      <td>".$zaznam['cas']."</td>
      <td>".$zaznam['cas_zmeny']."</td>
      <td>".$zaznam['cas_unix']."</td>
    </tr>
  ";
}
echo "</table>";
?>

A jak jsem již říkal, formát času můžeš měnit pomocí funkce DATE_FORMAT, její popis najdeme v manuálu – zde. Praktický ukázka přímo v dotazu pro přehlednost.

Příklad 3:

<?php
/*
* Připojení na databázi...
*/
$Dotaz=mysql_query("
      SELECT
         `id`, `jmeno`, DATE_FORMAT(`cas`, '%Y %m %d') AS `cas`
      FROM `test`
      ") or die ("Chyba v SQL dotazu: " . mysql_error());
// atd...
?>

Z DATETIME můžete získat unixové časové razítko a unixového časového razítka můžeme zístat DATETIME pomocí funkcí UNIX_TIMESTAMP a FROM_UNIXTIME. V databázi tedy nemusíme mít 2× stejný údaj, můžeme si vybrat jednu variantu a tu používat.

Krátký příklad, kde použiju obě tyto funkce, navíc funkce FROM_UNIXTIME umí i rovnou formátovat výstup stejně jako funkce DATE_FORMAT().

Příklad 4:

<?php
/*
* Připojení na databázi...
*/
$Dotaz=mysql_query("
      SELECT
       `id`, `jmeno`,
       UNIX_TIMESTAMP(`cas`) AS `UnixRazitkoZDateTime`,
       FROM_UNIXTIME(`cas_unix`) AS `DateTimeZCasovehoRazitka`,
       FROM_UNIXTIME(`cas_unix`,'%d.%m.%Y') AS `DateTimeZCasovehoRazitkaFormatovany`
      FROM `test`
      ") or die ("Chyba v SQL dotazu: " . mysql_error());

// Sloupcům přiřadím alias, tak dlouhý je kvůli přehlednosti - pojmenování je na vás.

echo "
  <table>
    <tr>
      <th>ID</th>
      <th>Jméno</th>
      <th>Unix z datumu</th>
      <th>Datum z Unixu</th>
      <th>Datum z Unixu formátovaný</th>
    </tr>
  ";
while($zaznam=mysql_fetch_array($Dotaz)){
  echo "
    <tr>
      <td>".$zaznam['id']."</td>
      <td>".$zaznam['jmeno']."</td>
      <td>".$zaznam['UnixRazitkoZDateTime']."</td>
      <td>".$zaznam['DateTimeZCasovehoRazitka']."</td>
      <td>".$zaznam['DateTimeZCasovehoRazitkaFormatovany']."</td>
    </tr>
  ";
}
echo "</table>";
?>

MySQL má spoustu možností, umí datumy sčítat, počítat rozdíly mezi nimi a spoustu dalších věcí, pokud máte zájem – podívejte se do MySQL manuálu – vaše nová bible ;-)


<Starší | tento článek | Novější>

Napsáno: 20. 12. 2007, 10:15 | Přečteno: 42562x | Kategorie: PHP School | Napsal: peCan.cz |
Víte jakej je rozdíl mezi cirkulárkou a hajzl papírem? Žádnej, stačí chvilka nepozornosti a prst je v prdeli!
Petr Graubner | ICQ- 264912843, Jabber - pecan27@njs.netlab.cz | RSS | Šlape na miniRS | Styl CoolStyle | Zpracováno za: 0.135s | Počet SQL dotazů ve fóru: 1 | Admin