Trenger hjelp til å "regne" litt med datetime

[MirusMentis]

Har en tabell i en mysql database hvor jeg registrerer en verdi 30 ganger i timen (hvert andre minutt)

 

Dump av tabellen ser slik ut:

http://gmx.no/strom/usage_alle.php

 

Nå ønsker jeg å lage div grafer ut av disse registreringene.

Ønsker da å f.eks regne ut så jeg har 1verdi pr time, slik at jeg ender opp med et datasett 24 rader/verdier, fra de siste 24 timer.

 

Jeg har tankegangen klar, men klarer ikke å utrykke dette i php.

Tenker som slik:

 

-hente ut alle verdier i tidsrommet grafen skal bestå av. (for 24 timer blir det slik: select * from table order by id DESC LIMIT 720)

 

-Loop igjennom alle verdier pr time, og regn ut en gjennomsnittsverdi pr time. Her aner jeg ikke hvordan jeg skal kode dette.

 

Tenkte litt å formatere timedate om til å kun være timen, da kan jeg "gruppere" alle som har lik time, summere de og dele på antallet for å få verdien.

 

$time = date('H',strtotime($row1['time'])); // Henter ut kun H (dvs: timen)

 

Men hvordan jeg skal kunne gjøre dette, samtidig som jeg "tar med meg" verien i 'watt' feltet vet jeg ikke.. Må jeg ha et 2 dimensjonellt array?

 

Trenger sårt en dytt i riktig retning her.

 

Grafen for siste 10 timer ser slik ut nå, inneholder 300 punkter..

http://gmx.no/strom/siste10.php //Obs bruker 10-20 sek på å laste pga mengden data.

Endret 10. desember 2009 av semtex

[quantum]

Dersom du har f.eks. disse dataene (fem samples pr time her)

 

id	watt	regtime
1	100	2009-12-11 22:14:26.0
2	130	2009-12-11 22:14:38.0
3	120	2009-12-11 22:15:11.0
4	120	2009-12-11 22:15:15.0
5	110	2009-12-11 22:15:22.0
6	112	2009-12-11 23:15:28.0
7	113	2009-12-11 23:15:36.0
8	313	2009-12-11 23:15:43.0
9	244	2009-12-11 23:15:54.0
10	12	2009-12-11 23:16:27.0
11	132	2009-12-12 00:16:34.0
12	32	2009-12-12 00:16:40.0
13	33	2009-12-12 00:16:50.0
14	332	2009-12-12 00:16:55.0
15	222	2009-12-12 00:17:02.0

 

så vil

 

select avg(watt), hour(regtime) from data group by hour(regtime) where ...

 

gi deg

 

avg(watt)	hour(regtime)
150.2000	0
116.0000	22
158.8000	23

 

hvordan eller hvorfor i php ... ingen aning :-P

[MirusMentis]

Trodde ikke man kunne kjøre slikt i selve Sql query`n, men der lærte jeg noe nytt!

Og det fungerte ypperlig.

 

Takk!

 

Jeg slang på "where day(regtime) = 11" // For dagens dato.

 

Men det fungerte ikke...

 

select avg(watts), hour(time) FROM `14e` group by hour(time) WHERE dayofmoth(time) = 6

 

SQL-spørring:

 

SELECT avg( watts ) , hour( time )

FROM `14e`

GROUP BY hour( time )

WHERE day( time ) =6

LIMIT 0 , 30

 

MySQL sa:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE day(time) = 6

LIMIT 0, 30' at line 1

[quantum]

Trodde ikke man kunne kjøre slikt i selve Sql query`n, men der lærte jeg noe nytt!

Og det fungerte ypperlig.

 

Takk!

 

Jeg slang på "where day(regtime) = 11" // For dagens dato.

 

Men det fungerte ikke...

 

*panneklask*, du må ha where før group by ... ikke omvendt som jeg antydet. Dessuten vil limit fungere på resultatsettet, som jo aldri blir større enn 24 om du grupperer på timer ... du må ta avgrensningen av samples fra de siste 24 timene med i where-klausulen.

 

Etter min mening er det bad practice å legge noe særlig logisk mening i sånne limit-avgrensninger, du ser jo en av sideeffektene her, og dersom man har det som generell vane går det dårlig om man glemmer seg når man jobber på en tabell med ikke-sekvensiell pk. Limit er fint til å avgrense idiotisk store resultater på en tilfeldig måte sånn at ting ikke går i stå, eller til å lage bla-neste-forrige-funksjonalitet, men da bør du sortere på en kolonne som brukeren kan forholde seg til, f.eks. timestamp'en.

Endret 11. desember 2009 av quantum

[quantum]

PS

 

Husk også på at day(regtime) gir dagens nummer innenfor en eller annen måned. Så hvis du har mer enn én måneds sample, risikerer du å få snittet for alle samples i f.eks. 11.11 OG 11.12 ... eller enda værre om du har data lengre tilbake i tid.

 

Det samme gjelder jo grupperingen på hour, skal du lengre tilbake i tid enn 24t, så må du ta høyde for dét ved å utvide grupperingen. Eks:

 

select count(*), avg(watt), concat(hour(regtime) , day(regtime)) hourday from data where 1=1  group by hourday

 

... ovenstående ryker jo når du går lengre enn én måned tilbake, men jeg vet ikke hvor langt tilbake du skal, du ser vel pointet og kan ta den høyden du trenger langs de samme linjene.

Endret 11. desember 2009 av quantum

[MirusMentis]

*panneklask*, du må ha where før group by ... ikke omvendt som jeg antydet. Dessuten vil limit fungere på resultatsettet, som jo aldri blir større enn 24 om du grupperer på timer ... du må ta avgrensningen av samples fra de siste 24 timene med i where-klausulen.

 

Da fungerte det straks bedre. Limit kommer av seg selv da jeg gjør dette fra phpMyadmin.

 

Etter min mening er det bad practice å legge noe særlig logisk mening i sånne limit-avgrensninger, du ser jo en av sideeffektene her, og dersom man har det som generell vane går det dårlig om man glemmer seg når man jobber på en tabell med ikke-sekvensiell pk. Limit er fint til å avgrense idiotisk store resultater på en tilfeldig måte sånn at ting ikke går i stå, eller til å lage bla-neste-forrige-funksjonalitet, men da bør du sortere på en kolonne som brukeren kan forholde seg til, f.eks. timestamp'en.

Datt litt av lasset her.. Limit kom fra phpmyadmin.. Jobber særdeles lite med databaser, dette er bare et hobby prosjekt som skal spytte ut grafer over strømforbruket hjemme.

 

PS

 

Husk også på at day(regtime) gir dagens nummer innenfor en eller annen måned. Så hvis du har mer enn én måneds sample, risikerer du å få snittet for alle samples i f.eks. 11.11 OG 11.12 ... eller enda værre om du har data lengre tilbake i tid.

 

Det samme gjelder jo grupperingen på hour, skal du lengre tilbake i tid enn 24t, så må du ta høyde for dét ved å utvide grupperingen. Eks:

 

Jepp, ser den der.. Derfor jeg vil ha med day() = aktuell dato, og evt slenge på AND month() med aktuell måned.

 

select count(*), avg(watt), concat(hour(regtime) , day(regtime)) hourday from data where 1=1  group by hourday

Den der feilet.. Men det gjør ikke noe, den du nevnte først fungerer akkurat slik den skal.

 

... ovenstående ryker jo når du går lengre enn én måned tilbake, men jeg vet ikke hvor langt tilbake du skal, du ser vel pointet og kan ta den høyden du trenger langs de samme linjene.

 

Planen er å lage 4 grafer (/datasett)

1: Siste 24t, med times intervall - 24 verdier.

2: Siste 7 dager, usikker på intervall og verdier som er fornuftig.. dagsintervall blir litt for lite. Går det ann å bruke hver n`te time?

3: Siste 30 dager, med dagsintervall, 30 verdier.

4: Siste 12mnd, med 1mnds intervaller, 12 verdier.

 

Pr nå har jeg bare data for de siste 2-3 dager, så det blir vanskelig å teste ut graf #3 og #4

 

PS, jeg vil nok også måtte fange opp hva aktuell time er pr "nå", f.eks om jeg laster siden kl 07 om morgenen, så vil grafen for de siste 24t, kun vise de 7 timene det aktuelle døgnet.

Endret 11. desember 2009 av semtex

[quantum]

Datt litt av lasset her.. Limit kom fra phpmyadmin.. Jobber særdeles lite med databaser, dette er bare et hobby prosjekt som skal spytte ut grafer over strømforbruket hjemme.

...

select count(*), avg(watt), concat(hour(regtime) , day(regtime)) hourday from data where 1=1  group by hourday

Den der feilet.. Men det gjør ikke noe, den du nevnte først fungerer akkurat slik den skal.

 

 

Den funka hos meg, men så har vi jo ikke helt identiske databaser da :-)

 

Ifm limit sikta jeg til den første spørringen du hadde

 

select * from table order by id DESC LIMIT 720

 

Her er det order by og limit som brukes til å velge ut data fra de siste 24 timene, dvs. de 720 radene med høyest id. Når du grupperer på time får du jo et resultatsett med max 24 rader, og limit-begrensningen vil ikke ha noen virkning, du vil isteden regne på alle radene i tabellen, så langt tilbake i tid som dataene strekker seg, og det er jo ikke det du er ute etter.

[MirusMentis]

Har da denne spørringen for å få ut "siste 24timer, med 1t intervaller"

select CAST( avg( watts ) AS signed )AS watts, hour(time)AS time FROM 14418_usage where day(time) = '$dag' group by hour(time)

Cast`er om til signed int for å unngå desimaler.

 

For siste 30dager, med 1dags intervaller har jeg denne:

(som er identisk med den over, bare byttet ut timer med dager, og dager med mnd)

select CAST( avg( watts ) AS signed )AS watts, day(time)AS time FROM 14418_usage where month(time) = '$mnd' group by day(time)

 

Har også en som lister ut siste 7 dager med 1times intervaller. Men det blir for mange verdier til at rammerverket som lager grafene takler det.

Her har jeg bare prøvet og feilet med "Group By" Og fikk den til slutt til å gruppere pr time, og samtidig dag.

select CAST( avg( watts ) AS signed )AS watts, hour(time)AS time, time as tid FROM 14418_usage where weekofyear(time) ='$uke' group by day(time), hour(time)

 

Det jeg mangler er å finpusse "7dager" slik at jeg ender opp med ca 30-50 verdier.

Og å finne ut en for siste 365 dager. Kunne hatt snitt pr mnd, men blir litt for lite. 2-3verdier pr mnd, men det lar seg kanskje vanskelig gjøre i en MySQL query?