Archived Forum

There is if you use MySQL to store your feed data

Hi

Am I correct in believing that there has been a move away from using MySQL due to the database sizes?

Regards

Dave

Depends whether your talking about Emoncms.org hosting or your own on a Raspberry Pi for example

I currently use my pi for the emonCMS and SQL but theres no reason why I couldn't utilize an Ubuntu server for hosting the sql and emoncms.

Regards

Dave

Mine's on a Pi with a USB hard drive and I use MySQL as the storage engine for all my feeds if you're interested I can show you how I implement hourly, daily and monthly feeds.

Please, that would be fantastic.

Regards
Dave

Bra1n,

I'm interested in that too.

Thanks,

Bill

+1.  Me too!

OK just woke up I'll get something up later today. I use phpmyadmin to manage mysql and as I said earlier your source data needs to be stored in mysql.

There are other ways of doing this, but my method uses database Views which behave as a sort of virtual table but are actually evaluated whenever the View is accessed. The data is not stored separately. 
Because this method uses SQL 'group by' functions you have to limit the timescale of the data that is evaluated. Otherwise, the sums will be wrong. For example, the Daily feed only evaluates the latest 28 days to avoid the same day in 2 different months being summed together. Similarly, the hourly feed only evaluates the previous 24 hours.

In my examples below, feed 1 is Hourly, feed 2 is Daily and feed 3 is Monthly. Obviously you need to substitute your own feed numbers in the SQL statements, using feed numbers that are not currently in use. Feed 9 in my example contains the source data, which in my case, is rainfall data from a tipping bucket rain gauge, usually recorded once per minute.

I run the SQL statements in PhpMyAdmin by selecting the SQL Tab and pasting in the SQL and pressing 'GO', you could also do it from the console once logged into MySQL

Hourly Rainfall View :-

*****************
DROP VIEW IF EXISTS emoncms.feed_1;
CREATE VIEW `feed_1` AS select unix_timestamp(cast(from_unixtime(`feed_9`.`time`) as datetime)) AS `time`,sum(`feed_9`.`data`) AS `data` from `feed_9` where ((unix_timestamp(now()) - `feed_9`.`time`) < 86400) group by hour(from_unixtime(`feed_9`.`time`)) order by `feed_9`.`time`;
*****************

Daily Rainfall View :-

*****************
DROP VIEW IF EXISTS emoncms.feed_2;
CREATE VIEW `feed_2` AS select unix_timestamp(cast(from_unixtime(`feed_9`.`time`) as datetime)) AS `time`,sum(`feed_9`.`data`) AS `data` from `feed_9` where ((unix_timestamp(now()) - `feed_9`.`time`) < 2419200) group by dayofmonth(from_unixtime(`feed_9`.`time`)) order by `feed_9`.`time`;
*****************

Monthly Rainfall View :-

*****************
DROP VIEW IF EXISTS emoncms.feed_3;
CREATE VIEW `feed_3` AS select unix_timestamp(cast(from_unixtime(`feed_9`.`time`) as datetime)) AS `time`,sum(`feed_9`.`data`) AS `data` from `feed_9` where ((unix_timestamp(now()) - `feed_9`.`time`) < 31536000) group by month(from_unixtime(`feed_9`.`time`)) order by `feed_9`.`time`;
*****************

In order to do something with these new feeds, you need to add entries to the feeds table, again using your own corresponding feed ids and labels etc.

e.g:-

*****************
INSERT INTO `emoncms.feeds`(`id`, `name`, `userid`, `tag`, 'public') VALUES (1,'Hourly Rainfall',1,'Weather Station',0)
INSERT INTO `emoncms.feeds`(`id`, `name`, `userid`, `tag`, 'public') VALUES (2,'Daily Rainfall',1,'Weather Station',0)
INSERT INTO `emoncms.feeds`(`id`, `name`, `userid`, `tag`, 'public') VALUES (3,'Monthly Rainfall',1,'Weather Station',0) 
*****************

You could leave it at this and would be able to plot the graphs but if you also want to display the current values (which are currently null in the feeds table for these 3 new feeds) you need a process to update those numbers.

This is bit more complicated as first you need to enable the MySQL Event Scheduler which is disabled by default :

http://dev.mysql.com/doc/refman/5.5/en/events.html

The following SQL turns on the event scheduler as long as the database user has permissions to enable it :-

****************
SET GLOBAL event_scheduler = ON;
****************

The event table in the mysql database is where events are triggered from and their current status can be viewed there (last run time etc). Be aware no errors will be reported if the event fails for any reason and as it is a global operation in the database, the SQL it runs needs to infer which database it needs to run on.
I update the values every 10 minutes but that is up to the user to decide what's appropriate for their data.

e.g. :-

***************
delimiter |

CREATE EVENT hourly_rainfall
    ON SCHEDULE
      EVERY 10 MINUTE
    COMMENT 'Checks current hourly, daily and monthly rainfall in views feed_1, feed_2 and feed_3 then updates corresponding feed in feeds table'
    DO
    BEGIN
        UPDATE emoncms.feeds SET `time`=NOW(),`value`= (SELECT CASE WHEN (SELECT `data` FROM emoncms.feed_1 WHERE(UNIX_TIMESTAMP(NOW())-`time`)<3600) IS NULL THEN 0 ELSE (SELECT `data` FROM emoncms.feed_1 WHERE(UNIX_TIMESTAMP(NOW())-`time`)<3600) END)
          WHERE `id` = 1;
        UPDATE emoncms.feeds SET `time`=NOW(),`value`= (SELECT CASE WHEN (SELECT `data` FROM emoncms.feed_2 WHERE(UNIX_TIMESTAMP(NOW())-`time`)<86400) IS NULL THEN 0 ELSE (SELECT `data` FROM emoncms.feed_2 WHERE(UNIX_TIMESTAMP(NOW())-`time`)<86400) END)
        WHERE `id` = 2;
        UPDATE emoncms.feeds SET `time`=NOW(),`value`= (SELECT CASE WHEN (SELECT `data` FROM emoncms.feed_3 WHERE(UNIX_TIMESTAMP(NOW())-`time`)<31536000 order by `time` desc limit 1) IS NULL THEN 0 ELSE (SELECT `data` FROM emoncms.feed_3 WHERE(UNIX_TIMESTAMP(NOW())-`time`)<31536000 order by `time` desc limit 1) END)
        WHERE `id` = 3;
    END |

delimiter ;
***************

I've used '***************' to delimit & indicate the SQL statements that would be copied & pasted into MySQL (obviously don't include these asterisks when copying & pasting)

The way the SQL statements are constructed means you won't see the data until the time period is completed. So for example, you won't see data in the hourly view for say 09:00 to 10:00 until after 10:00. This could probably be changed, but suits my purposes as it currently stands.

Thanks for the info, Bra1n!

I will write documentation for doing this with phpfina feeds for local and emoncms.org. I'll update back here soon.

Sounds like an excellent idea Trystan, I look forward to trying it on emoncms.org

Thank you very much Bra1n, for posting the SQL code. Going forward, is there much benefit on setting up a CMS server hosted on a proper server and utilize MySQL rather than using the standard setup on the emonpi?

Regards

Dave

I don't know which version of emoncms you are using but as far as I'm aware the more recent versions have SQL as a storage engine option certainly the 9 RC2 | 2015.09.15 version I'm using does and that is by no means the latest. It may be worth upgrading your existing setup if you're on an earlier version. It runs well on my Pi2 setup and did on my previous Pi1 both utiising external USB HDDs. I have also run the same version from SD Card but a power outage killed that SD Card eventually.

A slight correction to the Daily Rainfall View as I realised that since the clocks went forward the daily rainfall was showing for the previous day so I've added an hour to the data in the view definition, hopefully this will still be ok when the clocks go back ;)

Daily Rainfall View :-

*****************
DROP VIEW IF EXISTS emoncms.feed_2;
CREATE VIEW `feed_2` AS select unix_timestamp(cast(from_unixtime(`feed_9`.`time`) as datetime) + INTERVAL 1 HOUR) AS `time`,sum(`feed_9`.`data`) AS `data` from `feed_9` where ((unix_timestamp(now()) - `feed_9`.`time`) < 2419200) group by dayofmonth(from_unixtime(`feed_9`.`time`)) order by `feed_9`.`time`;
*****************