Home Automation Domotica Forum Europe, Bwired Forum

So you are still working on this.
Put an example record in the topic, that makes it easier to analyze.
But I think your GROUP BY by is not right, make this "GROUP BY date, hour(date)"
Pieter

Now I get the right outputs:)

Last day...
SELECT CONCAT(DATE_FORMAT(datum, '%Y-%m-%d %H'),':00') AS tid, sum(watt) FROM energi
WHERE datum >=date_sub(current_date(),interval 1 day)
GROUP BY DATE_FORMAT(datum, '%Y-%m-%d %H')
ORDER BY DATE_FORMAT(datum, '%Y-%m-%d %H') ASC;

1 Month back...

SELECT DATE_FORMAT(datum, '%Y-%m-%d') AS tid, sum(watt) FROM energi
WHERE datum >=date_sub(current_date(),interval 30 day)
GROUP BY DATE_FORMAT(datum, '%Y-%m-%d')
ORDER BY DATE_FORMAT(datum, '%Y-%m-%d') ASC;

/Tony

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">I also log my energy once a minute, but I'm not adding a record every minute in the database. I create one record for every hour but update it every minute.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Maybe this is a better way to shrink entry in the sql.
Do you have any hint how to úppdate to correkt hour? How is the table designed?
/Thanks Tony

Hi Tony,
Yes As I told above, I'm not interested in a detailed logging, If I want that I can set an option button in my Domoticasysteem and I get readings every minute. I have 24 records a day, what I do is select the current date and hour record from my database. Lets say today hour 10, if the select finds a record I update the record with current energy consumption accumulate all the new totals like power consumption today, total etc. If the select does not find a record this means a new hour has began and I add a new record to the database.
Its all very simple.

Thanks Pieter.

I think I am close the total solution.
First check in the database if there is any record at this hour.
select * from energi where hour(datum) = hour(CURRENT_TIMESTAMP()) and day(datum) = day(CURRENT_TIMESTAMP())

/Tony

Right, once you got the record you can update the fields with the current reading (adding hourly usage, daily usage etc)

Hi.

I want to try out this way of monitoring the energy meter. I have ordered every component, but instead of a tai8585 i recveived a tai8586.

I now need some electronics help on how to connect the 8586 to the mk120. any ideas appreciated? a schematics would be great.

Thanks in advance
.

to connect the 2 boards, does it matter how the BC547 is orientated. I suppose you remove the reed contact and connect the bc547 directely to the 2 pads. Should the emitter be conencted to the the signal side of the ds2423? ro is it the collecter that provide the "low-going" pulse?
Thanks in advance!

Connect it to J11 or J27, the TAI8586 has two counters.

sorry to keep bothering you, but should the emittor or collector of the BC547 be connected to the DS2324?
thanks!

I don't know the TAI8586, it has no read contact but instead of that two optocoupler input contacts. If you connect the BC547 to one of those connectors it should be working I guess. With the TAI8585 you are right, but this a different schematic. You have to experiment with it, I think if you shortcut J11 or J27 for a moment the counter is counting +1?

The TAI8586 has two optocoupler input contacts. To activate the optocoupler a small current is needed. You can try it with an old 1,5V battery.
I've used an amplifier (TLC271) together with a diode (BPW43) and a couple of resistors.