SQL Puzzler: now() vs time() = ?
Tento puzzler není zcela čistým SQL puzzlerem, protože se kromě SQL funkce now() týká také PHP funkce time(). Pracujete s PostgreSQL z jazyka PHP? V tom případě tento puzzler určitě bez váhání vyřešíte ;-)
Uvažujme triviální tabulku
CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
vlozeno TIMESTAMP(0) NOT NULL DEFAULT now(),
hodnota INTEGER
);
a spusťme následující jednoduchý PHP skript
<?php
// připojení k DB serveru
$conn = pg_connect(...);
// vložení řádku
$sql = 'INSERT INTO my_table (hodnota) VALUES (1)';
pg_execute($conn, $sql);
// načtení počtu "historických" záznamů
$sql = 'SELECT COUNT(*) AS c FROM my_table ' .
"WHERE vlozeno < '" . date('Y-m-d G:i:s') . "'";
$res = pg_query($sql);
$row = pg_fetch_assoc($res);
// vypiš počet
echo 'pocet: ',$row['c'];
pg_close($conn);
?>
Předpokládejme že před spuštěním skriptu je tabulka prázdná - co bude jeho výsledkem?
A jako již tradičně si dovolím vám nabídnout několik možností:
- 0
- 1
- 2
- ani jedno z výše uvedeného
Odpověď najdete v následujícím odstavci - záměrně je skryta abyste ji nemohli zahlédnout ani nechtěně koutkem oka. Stačí kliknout na "Odpověď" a text se objeví.
Odpověď
Správná odpověď je (d) - skript totiž zhruba v 50% případů vypíše "0" a zhruba v 50% "1". Problém je totiž v rozdílném způsobu ořezávání hodnot u funkcí now() a date().
SQL funkce "now()" hodnoty při ořezání na vteřiny, což je důsledkem použití typu TIMESTAMP(0), skutečně zaokrouhluje. Oproti tomu funkce date() standardně pouze volá funkci "time()" a tudíž hodnoty na sekundy nezaokrouhluje ale jednoduše ořezává.
| now() | now()::timestamp(0) | date('Y-m-d G:i:s') |
| 2010-03-26 02:08:46.37512+01 | 2010-03-26 02:08:46 | 2010-03-26 02:08:46 |
| 2010-03-26 02:08:46.83855+01 | 2010-03-26 02:08:47 | 2010-03-26 02:08:46 |
A právě z tohoto různého zaokrouhlování pramení problém ilustrovaný puzzlerem - pokud se trefíte do druhé poloviny vteřiny, funkce "now()" po zaokrouhlení vrátí "následující vteřinu" ale PHP funkce "date()" vrátí vteřinu aktuální. Tudíž dotaz zdánlivě nepochopitelně vypíše 0.
Naopak pokud se trefíte do první poloviny vteřiny, vše se chová dle očekávání a dotaz vypíše "1."
Zbývá uvést alespoň jeden způsob jak se tomuto problému vyhnout - například stačí nepoužívat PHP funkci "date" bez druhého (nepovinného) parametru, a použít funkci "microtime" například takto:
...
$time = round(microtime(true));
// načtení počtu "historických" záznamů
$sql = 'SELECT COUNT(*) AS c FROM my_table ' .
"WHERE vlozeno < '" . date('Y-m-d G:i:s', $time) . "'";
$res = pg_query($sql);
$row = pg_fetch_assoc($res);
...
Nyní už budou obě hodnoty zaokrouhlovány stejně a skript bude vždy vypisovat "1" ;-)
