The Bwired electricity monitor

[Willem4ever]

It could be localization kicking in ... but check your query as Pieter suggested (that's what i always do) and examen the output. Be advised that NOW() includes the time component as well.

[Spawn32]

seems that my problem was the Count field, i was storing them devided by 250 (250 counts pr Kwh) so the results in the sql fiels alwas included a " , ", mysql dosen seem to like that all is looking good now.

i am not very good at this sql stuff, so can somone help me out with a quary that does "consumtion pr hour for the last 24 hours", and one that does "consumption pr min. for the last 60 minutes" ? would be of great help for me

heres the one that does consumption pr day for the last 30 days :

strSQL = "SELECT Date, sum(Count), DAYNAME(Date) FROM energi_monitor GROUP BY Date ORDER BY Date DESC LIMIT 30"

Thanks for all the help
Morten

[Willem4ever]

Hi Morten,

In order to do that you should have a field with minutes and hours ...

Willem.

[Spawn32]

Yup, i have that, it's stored in a field called Time that is setup as Time

Morten

[Willem4ever]

ok, can you dump your database and mail it to me (willem4ever at yahoo dot co dot uk). I'll see what i can do for you. (results will be posted) I have some experience, but i'll have to do a little bit of playing to get it right.

[Bwired]

Hi Spawn,
As I told you before I create one record for every hour and update it every minute. So for one day I have 24 records added in my database.
Here is my select for a graph which shows the energy usages per hour in the Bwired house.

strSQL = "SELECT dayhour, wattage/1000 FROM energy ORDER BY id DESC LIMIT 24"

As you can see it's very simple
Regards Pieter

[tonlof]

Keep in mind that the 1-Wire TAI8585 only can count to 65535 before it resets to zero. As there are 480 flashes in 1 KWh the pulse counter will reset after counting 136KWh. I read the 1-Wire once every minute and store about 60 readings in one hour.

Do you reset the counter every minute after storing the countervalue in the mysql database?
I am using velleman 8055 usb board that have double counter and the max value is 65535. (Have 1000 flashes/KWh)
http://www.velleman.be/ot/en/product/view/?id=351346
Thanks for any help.

[Bwired]

Hi Ton,
No I'm not resetting the counter value to zero, you can handle that in your program. I read every minute the counter value also store that counter value. Once you have a starting count lets say 1.02 hour 65501 and the next count is getting at 1.03 hour 00101, the total flashes will be (65535 - 65501) + 00101 giving a total of 135 flashes as in your case 0.135 KWh.

Pieter Knuvers
www.bwired.nl Online House in the netherlands. Domotica, Home Automation.

[tonlof]

Thanks Pieter, my brain was not with me;)
The solution was to easy...
/Tony

[tonlof]

Am I on right way or is there any better way to do it?
Not sure if I miss one watt when every time when the counter reset.

Code: Select all

Dim latest As Integer = 0
Dim counter_value As Integer = 0
   
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Kontrollera.Tick
        counter_value = TextBox1.Text '(Here should the recived value from the counter)
        If counter_value < latest Then
            Debug.Print((65535 - latest) + counter_value)
        Else
            Debug.Print(counter_value - latest)
        End If
        latest = counter_value
End Sub

[Bwired]

Hi Tony,
This looks good, I don't think you are missing any count. Let it run and see!
To check you can add the counter setting of the powermeter as well and check if it runs equally.

[tonlof]

Thanks, I will compare it later when we have moved in into that house that have this digital powermeter. I just try to make the program in advance:) Later I will make a HomeSeer Plugin. Thanks for all great idéas.

[b_weijenberg]

Pieter,

You will mis 1 count;-)
It should be:
If counter_value < latest Then
Debug.Print((65536 - latest) + counter_value)
Else

If the latest count = 0xFFFF (65535 decimal) and the counter value increments with 1 the result will be 0x0000
(65536-65535) + 0 = 1

Bert

[Bwired]

Right Bert, everytime I forget the dawn Zero [:D]

[tonlof]

I'm having the logging function okey now. Saving every minute.
But I have some problem to read out correct data from mysql.
I want to list hourly data of the power consume.
I am trying "SELECT Date, sum(watt)/1000, DAYNAME(Date) FROM energie GROUP BY hour(Date) asc"
It give me wrong result, wrong value and not the last days data.

Tony