Home Automation Domotica Forum Europe, Bwired Forum

Can someone help with some examples for properly results for a graphic from electricity?

This the one for the last 60 minutes.
Could it be easier?

SELECT twatt, MINUTE(ttime) FROM elec ttime > SUBTIME(curtime(), '01:00:00') AND tdate = CURDATE() ORDER by tdate DESC, ttime ASC LIMIT 60"

After midnight, it will not show the previous day results...
I can'y see the logic with SQL... and want a easy table in SQL
for the moment, i have; date, time, devicecode, valueofcounter, watt
And i'm using chartdirector...

thanks!

Hi,

Here's an MS SQL example for a 24-hour power usage chart.
i : "index" or position on the x-axis.
x : the values on the x-axis (the hours like 0, 13, etc.)
y : the y-values (the power usage)

I use a datetime type for the time field and store a start- and endvalue in every record.

You'll have to do some rewriting for MySQL i guess (limit instead of top etc.) but if you get the bigger picture it won't be that hard to alter this to your needs.

Succes,

the TOP 24 is LIMIT 24 in MYsql btw... for other users..

I understand what you mean, but i want the results inversed.
With your example, the latest results are in the graph at the left side.
I want it from old -> new (Left -> right)

How about this then:
restrict the records returned by the WHERE, not the TOP and then you can use another field for sorting:

select
datediff(hour,[time],getdate()) as i,
datepart(hour,[time]) as x,
(EndValue-StartValue)/1000 as y
from data
where lgDeviceID = 'RFXP3' and datediff(hour,[time],getdate()) < 24
order by i desc