SQL - avg z timestamp

answered 2014-12-02 15:14:12 +0100

Josef Kokeš
2956 ●20 ●35 ●73

updated 2014-12-02 15:15:03 +0100

Typ TIMESTAMP je určen pro časový okamžik, ne pro ukládání doby trvání. Z toho vyplývá, že průměr z toho nedává smysl ("průměrný okamžik příchodu zákazníka je 26. června 2014 v 13 hodin 56 minut 27,721 sekund") a tudíž to databáze normálně nepodporují (i když bych nechtěl dát hlavu na špalek, že třeba neexistuje nějaká, která by to dokázala - v otázkách tohoto typu je nutné říct, pro kterou databázi je otázka určena).

Dále budu předpokládat, že skutečně ukládáte jen hodiny a minuty a všechno ostatní je "nastavené na nulu" (takže fakticky máte hodnoty typu "1.1.1900 hh:mm:00", ale interpretujete je jako "měřená operace trvala hh hodin a mm minut").

Normálně se to dělá jedním ze dvou způsobů, příčemž oba jsou database-specific:

1. Víme, jak databáze ukládá datum a čas. Pak můžeme vhodně odečíst nebo přetypovat a odečíst dva časové okamžiky a dostaneme rozdíl v nějaké jednotce, kterou pak přenásobíme na jednotku požadovanou. Například u Firebirdu víme, že datum a čas ukládá jako počet dní od nějakého počátečního data, takže pokud chceme dobu trvání operace v minutách, můžeme napsat něco jako SELECT (delka-CAST('1900-01-01 00:00:00' AS TIMESTAMP))*24*60 (předpokládáme, že vaše timestampy jsou uložené s datumem 1.1.1900).

2. Víme, že naše databáze má funkce pro rozsekání timestampu na dílčí části. Pak můžeme použít tyto funkce pro vytáhnutí hodin a minut a vhodné vynásobení: SELECT 60*EXTRACT(HOUR FROM delka)+EXTRACT(MINUTE FROM delka).

Na takto vypočítanou hodnotu už můžeme použít AVG, protože to už je normální desetinné číslo.

Připomínám, že kompatibilita napříč databázemi je nulová.