MySQL Forums
Forum List  »  Stored Procedures

consolidating temporal data
Posted by: Sean Coston
Date: January 07, 2018 10:38PM

Hi, this is my first post here.

Hoping for a bit of guidance. I have a homebuilt weather station that records data to a mysql database via an html post mechanism, processing the post in a PHP script on my web server.

The posts are sent every 10 seconds for wind, atm pressure, and station status data, & every 30 seconds for temperature, solar radiation, rain accumulation data.

Currently, I store the data in a series of tables with timestamps and datatype columns to identify unique records, retrieve that data back out of the database as needed for presentation to my weather station web pages.

I am running mysql on a linux VM along with the web server and I use MySQL workbench.

I currently employ several views that have been constructed in MySQL Workbench so that I run queries requested by the web server on smaller sets of data. The problem is when the queries are called, the server recalculates the views, so I get little to no benefit in performance. As I call the queries everytime the web page is updated, the update period for the web page to get contemporary data is longer than I would like.

What I'd like to do is mitigate the situation by using more data tables. One set of tables that stores data up to only 72 hr old to run the recent weather queries on, one set of tables to store "conditioned" data for retrieval by almanac-type web pages, and a third set of tables that will archive all the received data (which is what my views run on right now - these tables get large and thus why I am experiencing the performance hit when running min, max, and avg queries)

My plan is to consolidate data by running a stored procedure or a function every day after midnight that looks at data from 72 hr ago to 48 hr ago and hour by hour finds/computes the max, min, and average values for several types of the data for that hour, writes that data with discrete timestamps for max and min and hourly timestamps for the averages to a new table in the "conditioned" data table set. Then I want to write the original data to the third archive table and then delete it from the original web-post recording tables so they never contain data over 72 hours old.

Is this possible with stored procedures on the MySQL server? Or should I write more PHP code on the web server side? I have quite a bit of experience writing code in Access and some in PHP, but thought a procedure on the MySQL server might be more elegant.

Thanks in advance if anyone looks at this and decides to try an answer.

Sean Coston

Options: ReplyQuote

Written By
consolidating temporal data
January 07, 2018 10:38PM
January 08, 2018 12:17AM
January 09, 2018 01:55PM
January 09, 2018 05:27PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.