[Mysql] Group by en tijdsintervallen

[megamuch]

Situatie:

Programma krijgt op poort XXX of poort YYY data binnen. Elke keer als er data binnen komt, sla ik dat op in een database.

Nu wil ik een grafiek maken van de afgelopen 24 uur met een interval van 5 minuten. (Think MRTG)

Ik heb het gevoel er bijna te zijn, maar ik mis nog het 1 of ander.

SQL:

select date_format(senddate, '%Y-%m-%d %H:%i:00') as date, count(*) as total from inbound where `received` between '2008-08-21 17:15:00' and '2008-08-22 17:15:00' and port = xxx GROUP BY (12 * HOUR(received) + FLOOR(MINUTE( received) / 5 )) order by `received`

dit geeft het volgende resultaat:

code:

...knip...
2008-08-22 06:50:00     5
2008-08-22 06:55:00     2
2008-08-22 07:00:00     1
2008-08-22 07:05:00     4
2008-08-22 07:10:00     2
2008-08-22 07:17:00     2
2008-08-22 07:35:00     1
2008-08-22 07:40:00     2
2008-08-22 07:45:00     2
2008-08-22 07:53:00     1
2008-08-22 07:55:00     3
2008-08-22 08:00:00     3
...knip...

Het lijkt goed te gaan, maar dan staat er ineens 07:17 tussen. Ik wil geen 07:17, ik wil 07:15.

Daarnaast zoek ik nog de mogelijkheid om de query zo te schrijven dat 0 waardes ook worden meegenomen zodat het interval van 5 minuten er altijd is. (Zie het verschil tussen 07:17 en 07:35).
De bedoeling is dat ik altijd 24 x 12 = 288 rows als result terug krijg.
Dat laatste is nog wel in php op te vangen, maar als het direct in Sql kan...

Iemand een tip om dit varkentje te wassen?

[remco_k]

Wat is de inhoud van de tabel zonder de group by?

[Verwijderd]

Is het geen optie om die date naar een timestamp om te werken en al die timestamps op 5 minuten (dus op 300-tallen) af te ronden?

[Bas Jansen]

Je GROUP BY bevat iets anders dan je SELECT, dus je grouped wel by de tijd afgerond op 5 minuten, maar je select de onafgeronde tijd.

(begrijp je 't nog? )

[ Voor 6% gewijzigd door Bas Jansen op 22-08-2008 17:49 ]

[martijnve]

idd gewoon in de where controleren: tijd mod 300 = 0 (als je de tijd opslaat als unix timestamp maar dat doe je geloof ik al?)
(weet niet of dit valide syntax is in mysql of dat het met een % moet of dat je er mischien zelfs expliciet eoa functie voor aan moet roepen maar iets in deze richting)

edit:
en als je niet precies op 5 minuten wil beginnen kun je die 0 dus vervangen door de gewenste "afwijking"

[ Voor 32% gewijzigd door martijnve op 22-08-2008 17:57 ]

[megamuch]

Bas Jansen schreef op vrijdag 22 augustus 2008 @ 17:49:
Je GROUP BY bevat iets anders dan je SELECT, dus je grouped wel by de tijd afgerond op 5 minuten, maar je select de onafgeronde tijd.

(begrijp je 't nog? )

I hear ya, maar hoe kan ik zou die select er dan uit komen te zien? Ik zou niet weten hoe ik die select ook naar 5 minute intervals kan schrijven. (Maar ik zuig dan ook verschrikkelijk met SQL).

Verwijderd schreef op vrijdag 22 augustus 2008 @ 17:46:
Is het geen optie om die date naar een timestamp om te werken en al die timestamps op 5 minuten (dus op 300-tallen) af te ronden?

Ik ga ff kijken naar de unix_timestamp functie van Mysql

remco_k schreef op vrijdag 22 augustus 2008 @ 17:43:
Wat is de inhoud van de tabel zonder de group by?

Wat bedoel je precies? Tis een table met 28 columns, en flink wat prive info. Verder ook niet relevant voor het vraagstuk verder, gok ik.

[martijnve]

megamuch schreef op vrijdag 22 augustus 2008 @ 17:54:
[...]

Wat bedoel je precies? Tis een table met 28 columns, en flink wat prive info. Verder ook niet relevant voor het vraagstuk verder, gok ik.

Hij wil denk ik weten wat voor gegevens je hebt om mee te werken (dus niet de idd irrelevante prive gegevens, en dan denk ik voornamelijk hoe je de tijd opslaat)

[megamuch]

martijnve schreef op vrijdag 22 augustus 2008 @ 17:59:
[...]
Hij wil denk ik weten wat voor gegevens je hebt om mee te werken (dus niet de idd irrelevante prive gegevens, en dan denk ik voornamelijk hoe je de tijd opslaat)

Tijd is een datetime field van mysql met default notatie : yyyy-mm-dd hh:ii:ss
Kan daar ook niets aan veranderen helaas.

Ik ben even aan de slag gegaan met unix_timestamp:

nieuwe SQL

SQL:

SELECT unix_timestamp( senddate ) AS date, count( * ) AS total
FROM inbound
WHERE unix_timestamp( `received` ) >
unix_timestamp( now( ) ) -86400 AND unix_timestamp( `received` ) <
unix_timestamp( now( ) ) AND port = xxx  GROUP BY ( 12 * HOUR( received) + FLOOR( MINUTE( received) /5 ) ) ORDER BY received

Buiten het feit dat de query nu 30x langer duurt heb ik volgens mij hetzelfde resultaat. dus die mod 300 moet er nog ergens in verwerkt worden.

[RobIII]

megamuch schreef op vrijdag 22 augustus 2008 @ 18:12:
Tijd is een datetime field van mysql met default notatie : yyyy-mm-dd hh:ii:ss
Kan daar ook niets aan veranderen helaas.

Euh; het is een representatie (lees: weergave) van een bak enen en nullen. Of je die weergeeft als yyyy-mm-dd of mm-dd-yy of whatever; boeit niet. De betekenis (lees: data) blijft hetzelfde. Ik weet niet welke software je gebruikt om het weer te geven, maar daarin kun je het vast instellen en anders moet je eens kijken in je configuratiescherm bij Regional settings.

[megamuch]

RobIII schreef op vrijdag 22 augustus 2008 @ 18:19:
[...]

Euh; het is een representatie (lees: weergave) van een bak enen en nullen. Of je die weergeeft als yyyy-mm-dd of mm-dd-yy of whatever; boeit niet. De betekenis (lees: data) blijft hetzelfde. Ik weet niet welke software je gebruikt om het weer te geven, maar daarin kun je het vast instellen en anders moet je eens kijken in je configuratiescherm bij Regional settings.

Ik ben direct op de DB aan het werk met phumyadmin. Die notatie is overigens niet boeiend. Ik kan het converteren in m'n code naar wat ik wil. Wat ik veel interessanter vind is het feit dat ik nu een kleine 3 uur aan het googlen ben, maar het lijkt alsof ik de enige ben die dit soort geintjes wil uithalen.

Logparser van Microsoft doet het via QUANTIZE(TO_TIMESTAMP(date, time),3600) bijvoorbeeld om een overzicht per uur te genereren vanaf een (bijv) iisw3c logfile. Zo'n zelfde functie moet er toch ook wel zijn voor Mysql lijkt me.

Maargoed.. Maandag weer een dag

[Verwijderd]

megamuch schreef op vrijdag 22 augustus 2008 @ 18:12:
[...]

Ik ben even aan de slag gegaan met unix_timestamp:

nieuwe SQL

SQL:

SELECT unix_timestamp( senddate ) AS date, count( * ) AS total
FROM inbound
WHERE unix_timestamp( `received` ) >
unix_timestamp( now( ) ) -86400 AND unix_timestamp( `received` ) <
unix_timestamp( now( ) ) AND port = xxx  GROUP BY ( 12 * HOUR( received) + FLOOR( MINUTE( received) /5 ) ) ORDER BY received

Buiten het feit dat de query nu 30x langer duurt heb ik volgens mij hetzelfde resultaat. dus die mod 300 moet er nog ergens in verwerkt worden.

Misschien was ik net iets te onduidelijk, maar ik bedoelde eigenlijk dat je de datum in je select afrond op 5 minuten en op diezelfde datum een group by doet. Volgensmij moet dat vrij makkelijk te doen zijn, bovendien heb je dan nooit een een 7:17 of iets dergelijks in je resultset hangen (omdat die is afgrond naar 7:20). Die timestamp haalde ik aan omdat het daarmee makkelijker is af te ronden dan met een tijdstip in het hh:mm:ss formaat (althans, ik zou niet weten hoe).

edit:
volgensmij moet het afronden naar 5 min ongeveer zo:

ROUND(timestamp/300,0)*300

tenzij iemand integer divide in mysql weet, dan hoef je niet zo ingewikkeld te rounden...

[ Voor 6% gewijzigd door Verwijderd op 22-08-2008 18:52 ]

[Verwijderd]

Ik zou er een stored procedure van maken die de boel verzamelt per 5 minuten...

[winkbrace]

Ben benieuwd of je al een oplossing hebt gevonden.

Zoals al eerder gezegd in dit topic staat de oplossing van je probleem al in je GROUP BY. Ik begrijp trouwens niet waarom je 12 * HOUR(recieved) gebruikt?

Deze code is overigens ongetest, maar ik hoop dat het idee wel duidelijk is.

SQL:

select date_format(senddate, '%Y-%m-%d %H:%i:00') as date
,      HOUR(received)                             as hour
,      MINUTE( received)                          as minute
,      FLOOR(MINUTE( received) / 5 )              as minute_round
,      CONCAT( HOUR(received), ':', FLOOR(MINUTE( received) / 5 ) ) as time
,      count(*)                                   as total 
from   inbound 
where  received between '2008-08-21 17:15:00' and '2008-08-22 17:15:00' 
and    port = xxx 
GROUP BY (12 * HOUR(received) + FLOOR(MINUTE( received) / 5 )) 
order by received

Verder nog een antwoord op je tweede vraag: Maak een tabel met alle tijden per 5 minuten en LEFT OUTER JOIN je huidige tabel er mee. Het maken van die tabel zou ik overigens lekker in PHP doen.

[megamuch]

SQL:

SELECT round(unix_timestamp(`senddate`) /300, 0) *300 AS tempdate, count( * ) AS total FROM inbound WHERE `senddate` BETWEEN '2008-08-21 17:15:00' AND '2008-08-22 17:15:00' AND port =xxx GROUP BY tempdate

Bovenstaande werkt prima De truc is dus om idd de select al om te kitten. En het is nog snel ook. Nu alleen nog een truc om lege timeframes ook in de resultset te krijgen, maar ik denk dat dat niet gaat op deze manier.

Thanks for the help iedereen!

p.s. Sorry trouwens van de verwarring tussen senddate en received. Ik zat met 2 tabellen door elkaar te klooien.

[ Voor 10% gewijzigd door megamuch op 25-08-2008 12:10 ]

[Verwijderd]

Bedoel je

SQL:

WHERE `senddate` BETWEEN '2008-08-21 17:15:00' AND '2008-08-22 17:15:00' AND port =xxx 

te veranderen in

SQL:

WHERE (`senddate` BETWEEN '2008-08-21 17:15:00' AND '2008-08-22 17:15:00' OR 'senddate' = '0:00:00') AND port =xxx OR 

Weet alleen niet of de sql het voor jou doet.

[ Voor 0% gewijzigd door Verwijderd op 25-08-2008 12:14 . Reden: haakje verkeerd ]