Well Golly

I accidentally created a bunch of tables without setting the `id` field as autoincrement, having a primary key, and making the `datetime` field unique. Doing some of these things as separate steps doesn’t work because they are dependent on each other. So I combined them all into one command.

ALTER TABLE `values_Temperature` ADD PRIMARY KEY (`id`), MODIFY `id` INTEGER NOT NULL AUTO_INCREMENT, ADD UNIQUE KEY `datetime` (`datetime`);

I have been getting data for temperature and humidity from the California Irrigation and Management System (CIMIS), importing it into LibreOffice, manipulating the date field, and copying it into BBEdit to convert to sql files for import. That works fine for a years worth of data, but I wanted to get the last five years and not have to do any manual manipulation of the data.

The data from CIMIS comes in as a CSV file with the qc fields mostly blank. This is a problem when the last field is empty since the import function thinks that there is no data there. So I ignore the last field when creating the table. Also the field names have spaces and it is generally not common to use spaces in MySQL field names, so I replaced them with underscores. The qc fields all have the same name so I numbered them.

I want my data to have an ID column but I don’t want to have to add it to the CSV file. The import works fine if it is at the end of the table. You can always move it to the beginning with PHPMyAdmin later.

Stn Id,Stn Name,CIMIS Region,Date,Hour (PST),Jul,ETo (in),qc,Precip (in),qc,Sol Rad (Ly/day),qc,Vap Pres (mBars),qc,Air Temp (F),qc,Rel Hum (%),qc,Dew Point (F),qc,Wind Speed (mph),qc,Wind Dir (0-360),qc,Soil Temp (F),qc
125,Arvin-Edison,San Joaquin Valley,9/30/2014,0100,273,0.00, ,0.00, ,0, ,13.3, ,59.6, ,76, ,52.1, ,3.2, ,156, ,72.1,

CREATE TABLE `CIMIS_All` (
  `Stn_Id` int(4),
  `Stn_Name` varchar(50),
  `CIMIS_Region` varchar(50),
  `Date` varchar(10),
  `Hour_(PST)` varchar(4),
  `Jul` float,
  `ETo_(in)` float,
  `qc1` int(4),
  `Precip_(in)` float,
  `qc2` int(4),
  `Sol_Rad_(Ly/day)` float,
  `qc3` int(4),
  `Vap_Pres_(mBars)` float,
  `qc4` int(4),
  `Air_Temp_(F)` float,
  `qc5` int(4),
  `Rel_Hum_(%)` float,
  `qc6` int(4),
  `Dew_Point_(F)` float,
  `qc7` int(4),
  `Wind_Speed_(mph)` float,
  `qc8` int(4),
  `Wind_Dir_(0-360)` float,
  `qc9` int(4),
  `Soil_Temp_(F)` float,
  `id` int(8) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

When importing, I needed to compress the data since it exceeds the PHPMyAdmin 2MB limit. I also didn’t want to fool with the file at all so I when importing I skipped the first line using this selection in PHPMyAdmin.

Skip this number of queries (for SQL) starting from the first one: 1

I also used the default CSV import settings. After import I got this error because the last line in the CSV file is empty.
Invalid column count in CSV input on line 45434.

Otherwise the import went fine.

Now I need to get the time and date info into a datetime field. CIMIS is a bit weird in that they treat midnight as 2400 of the current day whereas MySQL treats it as 00:00:00 of the next day. So I just converted all of the 2400 to 2359. Then I add a minute to convert them to MySQL format. Since I asked for data through today, I got all of today’s times—even though there is no data for it. CIMIS puts an ‘M’ in the field which is imported as ‘0’. If air temp, soil temp, and relative humidity are all 0, that means there is no data for that time period, so I deleted those rows.

ALTER TABLE `CIMIS_ALL` ADD `datetime` DATETIME NOT NULL AFTER `id`;

UPDATE `CIMIS_All` SET `Hour_(PST)` = '2359' WHERE `Hour_(PST)` = '2400';
UPDATE `CIMIS_All` SET `datetime` = STR_TO_DATE(CONCAT(`Date`,`Hour_(PST)`), '%m/%d/%Y%H%i');
UPDATE `CIMIS_All` SET `datetime` = ADDTIME(`datetime`, '0:01:0') WHERE MINUTE(`datetime`) = '59';
DELETE FROM `CIMIS_All` WHERE `Air_Temp_(F)` = 0 && `Soil_Temp_(F)` = 0 && `Rel_Hum_(%)` = 0

Now that I have the data, I just export the fields I need into the tables that are already set up for the locations. I didn’t create a datetime field originally, so I’ll have to create it in the new table. Here’s an example where I import the temperature and humidity. You can import as many fields as you use.

INSERT INTO `CIMIS_Location` (`datetime`,`Air_Temp_(F)`,`Rel_Hum_(%)`) SELECT `datetime`,`Air_Temp_(F)`,`Rel_Hum_(%)` FROM `CIMIS_All`;

You can also automate this process by retrieving an XML file from the CIMIS server. You can retrieve it with this command.

wget -q -O CIMIS_data.xml  "https://et.water.ca.gov/api/data?appKey=YourKey&targets=70,71&startDate=2019-01-01&endDate=2019-01-05"

You can list as many targets as you want. The API key can be gotten from the CIMIS website. Once you have the data, you can parse it using XML parsing tools.

I get temperature data from sensors that report midnight as 00:00:00. I also get data from the State of California CIMIS system and they report midnight as 24:00:00. If I want to merge my data with the state data it doesn’t work. Plus datetime in MySQL isn’t happy about time being 24:00:00.

So to get around that I put the data into my tables with the time portion of the datetime field as 23:59:59. I use BBEdit to edit the raw CSV data so that it can be used in a MySql INSERT statement and it’s just one more find and replace to make it work.

For most purposes that is fine, but there are some calculations that I do on an hourly basis and the last reading of the day is yesterday in the CIMIS data compared to my sensors so I get some slightly different answers. Also, if I am missing data for my sensors, it is difficult to add the missing data.

The solution is obvious, just add a second to the CIMIS data. I couldn’t figure out a way to do that in BBEdit or in LibreOffice.

But after the CIMIS data is in the table you can convert dates with 23:59:59 by adding one second to them with a simple MySQL command.

UPDATE `values_Temperature_1` SET `datetime_temp` = ADDTIME(`datetime`, '0:0:01') WHERE SECOND(`datetime`) = '59'