0

For a smart metering application i would like to aggregate counter data every hour.

For this a query like 'select sum(value) as GasCounter from data where channel_id=8' gives the actual counter value.

For another display task i need this as data of channel 25 every hour in the same table 'data'. Therefor i have to add a dataset and put a valid unix-timestamp in the field 'timestamp' and the sum value in the field 'value'.

All this must be done every hour.

Can Mysql do all that just by using a routine?

What is the right routine sql? How do i create the routine? How is the trigger done so that the routine is executed everx hour?

I need your help on this!

Thank you!

Bernd Gewehr
  • 97
  • 1
  • 12
  • Why do you need to "save" the sum into a database? Why not to use that query every time but with an interval limitation in its where clause. Or group your query by hour(your_timestamp). – Gustavo Vargas May 09 '13 at 20:47
  • Putting aside Gustavo's legitimate question, I think you can do this with an event - but I'm not too familiar with events. I thought this would work, but no... CREATE EVENT e_sum ON SCHEDULE EVERY 20 SECOND DO INSERT INTO my_table SELECT NULL,SUM(i) FROM my_table; – Strawberry May 09 '13 at 20:54
  • There is a php middleware in action which expects the desired situation to show the data in a smart meter application as a graph. I can't change that so i need to fulfill the requirements... – Bernd Gewehr May 09 '13 at 20:55

1 Answers1

0

Create a procedure that process what you need to make your middleware happy and then use an event scheduler to call your procedure every one hour.

Gustavo Vargas
  • 2,497
  • 2
  • 24
  • 32
  • I did this: CREATE EVENT `Gas_sum` ON SCHEDULE EVERY 1 HOUR DO insert into data (channel_id, timestamp, value) select 25, unix_timestamp()*1000, sum(value)/1000 from data where channel_id =8 The Insert itself works as designed, but the event does not fire. Anything wrong to it? – Bernd Gewehr May 10 '13 at 16:02
  • Try to use 'SHOW PROCESSLIST' and check if your event-scheduler thread is active. If not you should actived it by issuing: 'SET GLOBAL event_scheduler = ON;' – Gustavo Vargas May 11 '13 at 11:33
  • SHOW PROCESSLIST returns nothing but a blank prompt. I used the set global command, but show processlist always gives an emtpy return. – Bernd Gewehr May 11 '13 at 16:30