Community Support
Hi,
i'm in the process of changing both mye weather station hardware and also the software 😉
I have been using Cumulus from Sandaysoft the last 6 years and have a lot of historical data in there.
Has anyone converted old data from Cumulus' Dayfile.txt into Weather Station already?
regards,
Oddne
Hello Oddne.
Seems nobody have done this for now. I know @rkarpa01 have succeeded in importing old historical values, but I don't think it's from cumulus data...
If you like Weather Station, please consider to make a review to help make it known. That would be the best way to thank me...
I had all my old data in a MySQL database. From there I reviewed the tables Pierre is using in Weather Station and added my data to the historic table. The "problem" with the weather station historic data is that it does only store single value for avg, min, max, med and std value for each measure in that table so the data has to be a bit prepared.
If you have a couple of records for me, I might be able to review and assist in "how-to" upload. However, cannot promise that it will work 100%.
Thanks for feedback!!
I was thinking about dumping the dayfile.txt into my mySQL database and the start converting, but would really like your input @rkarpa01
You can see the current dayfile here: http://oddne.net/met/dayfile.txt
Also attached an Excel-versions with headings 😉
I am not familar with the daily aggregate file from Cumulus and the file you have, does not contain a header line.
Here's what I did:
I have built a copy of the table wp_live_weather_station_datas_year that is in wordpress database by extracting the data and import that into my local MySQL database. For this you will need phpadmin or similar to access the wordpress database.
CREATE TABLE Weather.wp_live_weather_station_datas_year (
timestamp date NOT NULL DEFAULT '0000-00-00',
device_id varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
module_id varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
module_type varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '<unknown>',
measure_type varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
measure_set varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
measure_value decimal(20,10) NOT NULL,
UNIQUE KEY dly (timestamp,device_id,module_id,measure_type,measure_set)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
I identified the device_id, module_id, etc. from one generated history set which was built over night using phpadmin on the wordpress server:
SELECT distinct device_id, module_id, module_type, measure_type, measure_set FROM wp_live_weather_station_datas_year
Then I used my daily local MySQL data from my old station and derived the needed measures from them as Weather Station uses it (min, max, avg, dev - I did not include med) like this:
INSERT INTO Weather.wp_live_weather_station_datas_year SELECT date(DateTimeStamp), 'xz:00:00:00:00:01', 'm5:c0:00:00:00:01', 'NAModule5', 'uv_index', 'min', min(UV) FROM Weather.wp_rk_weather_data WHERE DateTimeStamp >= '2018-01-18 00:00:00' AND DateTimeStamp < '2018-01-18 00:00:00' GROUP BY 1 ORDER BY 1;
INSERT INTO Weather.wp_live_weather_station_datas_year SELECT date(DateTimeStamp), 'xz:00:00:00:00:01', 'm5:c0:00:00:00:01', 'NAModule5', 'uv_index', 'max', max(UV) FROM Weather.wp_rk_weather_data WHERE DateTimeStamp >= '2018-01-17 00:00:00' AND DateTimeStamp < '2018-01-18 00:00:00' GROUP BY 1 ORDER BY 1;
INSERT INTO Weather.wp_live_weather_station_datas_year SELECT date(DateTimeStamp), 'xz:00:00:00:00:01', 'm5:c0:00:00:00:01', 'NAModule5', 'uv_index', 'avg', avg(UV) FROM Weather.wp_rk_weather_data WHERE DateTimeStamp >= '2018-01-17 00:00:00' AND DateTimeStamp < '2018-01-18 00:00:00' GROUP BY 1 ORDER BY 1;
INSERT INTO Weather.wp_live_weather_station_datas_year SELECT date(DateTimeStamp), 'xz:00:00:00:00:01', 'm5:c0:00:00:00:01', 'NAModule5', 'uv_index', 'dev', std(UV) FROM Weather.wp_rk_weather_data WHERE DateTimeStamp >= '2018-01-17 00:00:00' AND DateTimeStamp < '2018-01-18 00:00:00' GROUP BY 1 ORDER BY 1;
Once all was as I expected it to be on my own database, I exported the data from the local year summary, made a copy of the wordpress table (in case of, you never know) and finally truncated the year table in wordpress and loaded my export file into it.
As your data is already on a daily base, you will need to find the min/max/avg values from your daily set and build that part accordingly by inserting directly instead of uses aggregates. If you have your realtime granular data you could import that and build like I did.
Hope it's clear what I did.
of course, I did not look at the Excel, apologies, will do that right now
So, I did have a look at the daily file from cumulus, and it seems not to contain minimum values which limits the plugin later to only the max and average values, so no min, stddev or med. If you still have the recorded data as well, you could load that and build all aggregates in your MySQL database like I described above.
Thanks a lot, @rkarpa01
This is just what I was looking for! Will dig more into this myself also 😉
Have been working with maintaining databases (Oracle mostly) for more than 20 years, and have some developer knowledge, so this should be doable 🙂
Then it should work out easy. Let me know if you need any help.
You guys are the absolute best!!!
@rkarpa01 : I just converted more than 6 years of temp data (max, min, avg) just as a test and it worked like a charm!! Thanks a lot for pointing me in the right direction. A quick look in the table wp_live_weather_station_datas_year made it actually quite easy to understand what needed to be done.
Now I just need to convert the rest, but now I know what to do 😉
Thanks @pierre-lannoy for tagging @rkarpa01
You're welcome @oddne ! All my thanks go to @rkarpa01 ????
If you like Weather Station, please consider to make a review to help make it known. That would be the best way to thank me...
Great! Enjoy the fantastic plugin.
If you have an open site, please post a link to your weather station. I am interested in having a look how others do things.
You can see it at www.kattebakken.no , but it’s in Norwegian except from some English text here and there. But I guess you will find your way around 🙂
Your site was great, plain and simple, and gave me inspirations for the graphs!
Thanks, Oddne. Great site. I like that. Your site is very fast, need to talk to my domain provider 🙂