Select ze selectu (where plané pouze pro poslední záznam v databázi) – MySQL – Fórum – Programujte.com

Ahoj všem,

prosím si o radu zkušenější z vás, kteří mají mozek na to si problém v hlavě vybavit a provést v něm emulaci mého problému. :-)

Cíl: Vytvořit SQL dotaz, který rozezná, zda poslední přidaný záznam v tabulce je starší než 3 hodiny.

Chci v PHP udělat skript pro CRON démona spouštěný jako pravidelná úloha. Řekl jsem si, že to chci jednoduché, aby veškěrou práci dělal SQL dotaz a na straně PHP už bylo jen pár řádků.

Problém je v tom, že se mi SQL dotaz nedaří vymodelovat tak, aby fungoval jak potřebuji.

Cílem: Vzít poslední záznam v databází (pravděpodobně nutno řešit nějak takto): SELECT *
    FROM table ORDER BY ID DESC LIMIT 1 a nad tímto selectem udělat další select, který se pokusí z toho posledního přidaného záznamu vyselektovat záznam starší 3h. Ten by by mohl vypadat takhle: SELECT datum,cas FROM table WHERE cas < (NOW() - INTERVAL 160 MINUTE) ORDER BY cas DESC Dotaz, který potřebuji vymodelovat by byl kombinací výše uvedených dotazů ale v pořadí, že napřed musí proběhnout selekce posledního záznamu v tabulce a potom až WHERE klauzule. Výsledkem SQL dotazu bude buď prázdná množina (v případě, že poslední záznam v databázi nebyl přidán před více než 3 hodinami) a nebo množina s jedním prvkem (v případě, že poslední záznam byl přidán před více než třemi hodinami).

Představte si, že sbírám do databáze data ze senzoru, např. teplotu a chci aby mě automaticky spouštěná úloha upozornila např. e-mailem na to, že do databáze nebyla za poslední 3 hodiny přidána žádná nová hodnota (výsledek SQL dotazu bude jeden záznam = podařilo se nalézt poslední záznam, který je zároveň starší než tři hodiny). Což pro mě znamená např. jít restartovat zařízení se senzorem, zkontrolovat připojení k internetu apod.

Můj dotaz vypadá takto, ale nefunguje, měl byste pro mě prosím někdo nějaký nápad?

SELECT datum,cas
FROM (
    SELECT *
    FROM table
    ORDER BY ID DESC LIMIT 1
) AS lastrow
WHERE cas < (NOW() - INTERVAL 180 MINUTE)
ORDER BY cas DESC

Nešlo by případně využít klauzule HAVING nebo konstrukcí ALL/ANY?

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition); 

#1 fix 

Tak nějak od pasu:

SELECT max(cas) AS newest FROM table HAVING TIMESTAMPDIFF(HOUR, now(), newest) >= 3;

Děkuji ti moc za tip.

To nebude fungovat, protože čas není svázán s datumem a MAX(cas) = "23:59", ale sloupeček datum by byl třeba 10 let starý. To by možná fungovalo, kdyby šlo sloučit na úrovni dotazu sloupečky MAX(CONCREATE(datum,cas)) AS datetime do jednoho.Zkusím vygooglil jestli by to nějak tak šlo sloučit, aby to nebyl cas a datum zvlášť, ale super nápad, snad to nějak vykoukám.

EDIT: Už asi vím jak to myslíš a fungovat to tak nebude. Ty se domníváš, že čas = timestamp, ale čas v mém podání = např. "15:26" :-D

V praxi jde o to, že pokud bude aktuálně 15:00 a zároveň poslední záznam bude mít datum mezi 12-15h, tak je vše v pořádku, dotaz vrátí nula výsledků = poslední záznam není zastaralý a vše běží. Pokud však bude poslední záznam stačí tj sloupeček "čas" bude mít hodnotu např. 10:25, tak už je starší než 3 hodiny a tudíž senzor 3h neposlal do databáze data = průšvih a SQL dotaz nalezne nad posledním záznamem takový záznam, který je zastaralý.

#3 fix
Sloupeček cas uděudělej typu TIMESTAMP, jak se sluší a patří. I ten čas se do něněj bude ukládat automaticky.

Použil bych Timestamp pro datum a čas jak poradil Kit. Pak by poslední záznam měl nejnovější datum a čas. Šel bych na to tak, že bych hledal záznam mladší 3 hodin a pokud neexistuje (= záznam dat selhal) poslat mail. Dá se to udělat pomocí (jen pseudo kód) select * from tabulka where datum a čas between now - 3 hod and now. Počet záznamů = 0 pak znamená, že za poslední 3 hodiny nebylo nic zaznamenáno. Ještě detail: Pro odečtení 3 hodin od aktuálního času použij funkce, jakmile uděláš datum a čas - 3h, začne se to chovat jinak, než očekáváš (vlastní zkušenost). Získávání záznamů za časový úsek dané délky před okamžitým časem jsem tu kdysi řešil (asi 2 roky?)

hu

Kit: Děkuji, konečně jsem pochopil jak funguje timestamp. Svůj dotaz je zdá se funkční, to se ověří až s prvním výpadkem.

hlucheucho: Díky, přemýšlel jsem nad tím, jak to nemít takto krkolomně a podmínku invertovat. V PHP bych tam fláknul jen v podmínce vykřičník a hotovo v tom dotazu jsem byl líný nad tím přemýšlet, když by to znamenalo obrátit celou logiku, tak jsem se na to vyprdnul.

Tvoje řešení se zdá lepší a jednodušší, ale kitovo věřím že bude fungovat a není v mé moci nějak jednoduše tvůj pseudokód převést do funkční podoby, aniž bych se s tím dvě hodiny zlobil, takže raději budu používat kitovu verzi, pokud bude fungovat.

Oba jste mi to svými zkušenostmi suprově vymysleli a zjednodušili, já bych zase do Prahy jezdil přes Moskvu. :-D Díky moc.

Díval jsem se do starších projektů, kde jsem to dělal pomocí subtime() 

SELECT * FROM tabulka WHERE datumcas BETWEEN SUBTIME(NOW(), 3:00:00) AND NOW();

Délka intervalu se načítala jako Time z input ve webu ve tvaru h:mm:ss, proto je tam 3:00:00.

Pokud za poslední 3 hod neexistuje žádný záznam (vrátí 0 záznamů), zapisování dat selhalo. Nevýhoda je, že vrací záznamy splňující podmínku a musíš si je spočítat na straně aplikace (Vracení záznamů bylo cílem v mé aplikaci, jejich počet mne nezajímal). Ještě by to šlo vylepšit pomocí count() kdy přímo vrátí počet záznamů v daném úseku: 

SELECT COUN() FROM tabulka WHERE datumcas BETWEEN SUBTIME(NOW(), 3:00:00) AND NOW();

Nezkoušel jsem to, ale fungovat by to mohlo. Z počtu záznamů se pak dá odhadnout četnost selhání záznamu dat. Např. pokud zapisuji každou minutu, za 3 hodiny by mělo být 180 záznamů. Pokud jich bude jen 60, asi je záznam dat vysoce nespolehlivý.

hu

#6 fix
nešlo by to takhle? 

SELECT * FROM tabulka 
WHERE CAST(CONCAT(Datum, ' ', Cas) AS datetime) > SUBTIME(NOW(), 3:00:00);

Snad z každé varianty, kterou jste mi poslali jsem si vzal kousek a slepil to v super jednoduchý dotaz.

Děkuji vám všem za pomoc.