SQL SELECT statements...? week/day/24h

[de.lesse]

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!

[Digit]

Hi,

Here's an MS SQL example for a 24-hour power usage chart.

Code: Select all

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

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,

[de.lesse]

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)

[Digit]

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