I am going to use a local and remote mysql database to host the weather information. So I had to think about which variables to store and at 1 minute resolution, how much space would I need to accomplish this, as I want to store the information year on year. So I will store the following information:-
- Date and Time of the reading
- Humidity Outside
- Temperature on the Humidity Sensor
- Temperature on the Barometric Sensor
- Humidity Inside
- Temperature Inside
- Wind Speed Average over 1 Minute
- Wind Gusting Speed
- Wind Lowest Speed
- Light Full
- Light Visible
- Light Infrared
- Barometric (Pressure)
This works out to 1,440 readings a day (60 minutes x 24 hours). Which is 525,600 readings a year, so making the database as small as possible is critical. For this I will be using the DECIMAL data type, so you can specify exactly how much information to store. So the rough table looks something like this:-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE readings ( time_stamp DATETIME, humidity_out DECIMAL(5,2), # HTU21D 0.0 to 100.0 for humidity temp1_out DECIMAL(5,2), # HTU21D -40.0 to 125.0 for temp temp2_out DECIMAL(5,2), # BMP180 humidity_in DECIMAL(5,2), # HTU21D 0.0 to 100.0 for humidity temp_in DECIMAL(5,2), wind_avg DECIMAL(5,2), # no data for this yet but would expect 0 to 100.00 range wind_gust DECIMAL(5,2), wind_low DECIMAL(5,2), light_full DECIMAL(7,1), # TSL2561 0.1 - 40,000+ Lux light_visible DECIMAL(7,1), light_infrared DECIMAL(7,1), barometric DECIMAL(7,1) # BMP180 0.02hPa (0.17m) advanced resolution mode ) |
So using a local database on a desktop PC via phpMyAdmin, I used a store procedure to generate the dummy information. First create and store the procedure:-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
use `pi`; DELIMITER $$ DROP PROCEDURE IF EXISTS SampleProc$$ CREATE PROCEDURE SampleProc() BEGIN DECLARE x BIGINT; DECLARE y BIGINT; SET y = 1; WHILE y <= 366 DO SET x = 1; WHILE x <= 1440 DO INSERT INTO `readings` VALUES (utc_timestamp() + INTERVAL x MINUTE + INTERVAL y DAY, TRUNCATE(RAND()*100,2), TRUNCATE(RAND() * 100,2), TRUNCATE(RAND() * 100,2), TRUNCATE(RAND() * 100,2), TRUNCATE(RAND() * 100,2), TRUNCATE(RAND() * 100,2), TRUNCATE(RAND() * 100,2), TRUNCATE(RAND() * 100,2), TRUNCATE(RAND() * 1000,1), TRUNCATE(RAND() * 1000,1), TRUNCATE(RAND() * 1000,1), TRUNCATE(RAND() * 1000,1)); SET x = x + 1; END WHILE; SET y = y + 1; END WHILE; END$$ DELIMITER ; |
Note: The dummy database name is “pi”. Then you can just call:-
|
1 |
call SampleProc(); |
Which will take a few minutes (~10 mins) to finish populating the database. You’ll notice on the stored procedure that I counted for 366 days, rather than 365, just to simulate what a leap year would be.
Final size on disk of the database is ~61MB. Plus a bit more for the unique index on the time_stamp column.
Issues encountered: 1. Day light saving time. I was originally trying to store the timestamp, but on the 30th March 2014 at 2am I would get 60 rows with exactly the same date and time and since the time_stamp field needs to be unique, this was a massive issue, so to cure the problem, I changed the store value to DATETIME and inserted the utc_timestamp() value, so I will probably need to convert the utc timestamp to localised time allowing for day light saving time if I need to refer to a date and time specifically.
2. Log files. During my testing and repeated procedure running, I managed to generate a >600MB log file. So disabling the log files on the testing and production machine will need to be done. Find the following in your my.ini or my.cnf file:-
|
1 2 3 |
# Replication Master Server (default) # binary logging is required for replication # log-bin=mysql-bin |
find the line “log-bin=mysql-bin” and place a # and space in front of it, this will disable the log file generation.
The next step will be see how fast the hour average calls are for the last 24 to 48 hours for the chart mapping.