Upozornění na narozeniny v SQL

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

Pokud máme v databázi uloženo datum narození uživatele, tak můžeme chtít zjistit, jestli nebude v nejbližší době slavit narozeniny. Dalo by se to udělat tak, že od aktuálního data odečteme datum narození a pokud zbytek po dělení 365 dá nějaké číslo o málo menší než nula 365, tak bude mít brzo narozeniny. Problém s tímto přístupem spočívá v tom, že kvůli přestupným rokům nemá každý rok 365 dní, takže by výpočet nebyl přesný. Buď by se dal počet přestupných roků mezi narozením a aktuálním datem zohlednit (to by ale bylo velmi pracné), nebo se dá použít trik:

SELECT DATEDIFF(CONCAT(
	YEAR(CURDATE()) + IF(SUBSTR(narozeni, 5) < SUBSTR(CURDATE(), 5), 1, 0),
	SUBSTR(narozeni, 5)
), CURDATE()) AS narozeniny_za
FROM uzivatele
HAVING narozeniny_za BETWEEN 0 AND 14

Do data narození uživatele dosadíme aktuální rok (nebo ten příští, pokud už letos uživatel narozeniny měl) a odečteme od něj aktuální datum. Výsledné číslo je počet dní, za které bude mít uživatel narozeniny.

Funkce respektuje přestupné roky, jediný problém je s uživateli, kteří se narodili 29.2. Tito chudáci v nepřestupný rok narozeniny prostě nemají, takže se pro ně vrátí hodnota NULL.

Pro vyhodnocení dotazu se bohužel nepoužijí indexy. Pokud by se použít měly, bylo by nutné den, měsíc a rok ukládat zvlášť a podmínku celou přepsat.

Věk lze zjistit jednodušeji.

Jakub Vrána, Řešení problému, 23.10.2009, diskuse: 20 (nové: 0)

Diskuse

ikona ady:

Promin, mozna jsem to vubec nepochopil ale neco jako tohle nefunguje?

SELECT DATEDIFF(narozeni, CURDATE()) where narozeni between DATE_ADD(CURDATE(), INTERVAL 31 DAY) AND CURDATE()

<em>anonymní</em>:

Ne, to by vypsalo jen lidi, kteri se teprve v aktualnim roce narodi...

ikona Jakub Vrána OpenID:

Skutečně jsi to vůbec nepochopil. I kdybys neprohodil podmínku, tak to vrátí jenom lidi, kteří se teprve narodí. Než příště něco napíšeš, tak si to vyzkoušej.

fos4:

Pokud se nepletu tak u takovych dotazu je vyuziti indexu nulove.
Nebylo by lepsi si ukladat triggerem do pomocneho sloupce za kolik dni budou narozeniny a na nej dat index ?

ikona Jakub Vrána OpenID:

Díky za upozornění. Myslel jsem na to, ale zapomněl jsem to do článku napsat. Doplnil jsem to alespoň teď.

Gimli2:

...Dalo by se to udělat tak, že od aktuálního data odečteme datum narození a pokud zbytek po dělení 365 dá nějaké číslo o málo menší než nula,...

Pokud dobre chapu, tak natum narozeni je vzdy mensi nez aktualni datum. Uvedeny rozdil bude tedy vzdy kladny. Jakykoli zbytek po deleni kladneho cisla kladnym (365) nemuze nikdy dat cislo mensi nez nula.

ikona Jakub Vrána OpenID:

Jako že číslo kongruentní modulo 365 :-).

Jasně, opravil jsem to.

Kajman:

Místo where by mělo být having. A když by se místo curdate dalo rovnou '2009-10-23', může se použít sql cache.

Škoda, že mysql stále neumí indexy nad výsledkem funkce, pak by se mohl udělat index jako date_format(narozeni,'%m%d') a tím by už byly rychlé dotazy, třeba něco jako...
select u.*
from   uzivatele u,
       (select date_format('2009-10-23', '%m%d') d1,
               date_format(adddate('2009-10-23', interval 14 day), '%m%d') d2) l
where  (date_format(narozeni, '%m%d') >= l.d1 and
        date_format(narozeni, '%m%d') <= l.d2)
       or ((date_format(narozeni, '%m%d') >= l.d1 or
            date_format(narozeni, '%m%d') <= l.d2) and l.d1 > l.d2)

ikona Jakub Vrána OpenID:

WHERE jsem opravil. To skoro vypadá, jako kdybych si to ani nevyzkoušel, co? :-)

Daniel Tlach:

"Tito chudáci v nepřestupný rok narozeniny prostě nemají" - zase takoví chudáci nejsme ;) Vlastně je to lepší, protože stárneme mnohem pomaleji než obyčejná smrtelníci :D

ikona Jakub Vrána OpenID:

Nalejou ti aspoň v hospodě? Já jsem se chtěl zeptat, jestli mezi čtenáři někdo takový je, pravděpodobnost mi vyšla 65 % (podle TOPlistu).

Daniel Tlach:

Nalejvaj a ani občanku nechtěj :)

papundeklová paní:

Už je to možná off-mísa, ale on v Sovětském svazu v letech 1930 a 1931 existoval i 30. únor
http://cs.wikipedia.org/wiki/30._%C3%BAnor
(jóó, v Rusku, tam je možný fšechno :-) )

ikona v6ak:

V tomto článku je i odkaz na věk a obávám se, že ten bude běžet normálně, takže to až taková výhra IMHO není :P

Jirka:

to lze snadno resit tak, ze se rok prohodi u data narozeni i u aktualniho data - v obou pripadech na rok, ktery prestupny je. nicmene cele ro reseni mi prijde hrozne krkolomne, ale nemam cas to zkoumat, takze nevylucuju, ze je nejlepsi :)

kriplozoik:

Mě by jako první řešení tohohle problému (předpokládám, že v MySQL) napadlo porovnávat počet dní v roce datumu narození a aktuálního datumu, tedy DAYOFYEAR(datum_narozeni) a DAYOFYEAR(CURDATE()). Problém s 29. únorem tu je, ale dal by se zvládnout (jak jsem k mému podivu zjistil, tak funkce na zjištění, jestli je přestupný rok, neexistuje ani v MySQL ani v PHP (ne mezi základními funkcemi, ale jakési knihovně MCAL). V MySQL by si šlo samozřejmě pomoci, např. výrazem: DAYOFYEAR(CONCAT(rok,"-12-31"))=366.

fos4:

v PHP: date("L");
v MYSQL pouze trikem ktery jsi popsal ty..

ikona Jakub Vrána OpenID:

Tento článek vznikl jako reakce na pokus vyřešit to právě pomocí DAYOFYEAR… V MySQL by se dalo použít ještě SELECT CAST(CONCAT(rok, '-02-29') AS date) IS NULL.

Mat:

Dobrý den,
zkoušel jsem tento SQL příkaz v Delphi5, ale hlásí mi to hybu "invalid use of keyword YEAR(CURDATE()) " nevíte v čem by mohl být problém?

příkaz:
SELECT DATEDIFF(CONCAT(YEAR(CURDATE()) + IF(SUBSTR(narozeniny, 5) < SUBSTR(CURDATE(), 5), 1, 0), SUBSTR(narozeni, 5)), CURDATE()) AS narozeniny_za FROM "'+databaze+'" HAVING narozeniny_za BETWEEN 0 AND 14

Děkuji za odpověĎ

ikona Jakub Vrána OpenID:

Dotaz je určen pro MySQL, v jiných databázích nemusí být tato funkce k dispozici.

Vložit komentář

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:

avatar © 2005-2018 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.