Fixing a MySQL table

December 10th, 2019

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`);

Import CSV data into MySQL with PHPMyadmin

December 5th, 2019

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.

MySQL Datetime

November 26th, 2019

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'

Using open source fonts on your web page.

June 10th, 2019

I saw a font that might be fun on a friend’s national parks website so I wrote up some notes to help them install and use it.

You can put the NationalPark folder in the root of your site. i.e. the same place that the wp-admin, wp-content, … folders are located or if you think you’ll want to use multiple fonts, create a font folder in the root.

Then add these lines to the end of your CSS file that is located in the theme that you are using. He’s using twentynineteen. It will be a different folder if you aren’t using the twentyninteen style.

/wp-content//themes/twentyninteen/style.css


@font-face {
 font-family: "NationalPark";
 src: url("/fonts/NationalPark/NationalPark-Regular.otf");
 }

.NationalPark {
  font-family: NationalPark;
}

@font-face {
 font-family: "NationalParkBold";
 src: url("/fonts/NationalPark/NationalPark-Heavy.otf");
 }

.NationalParkBold {
  font-family: NationalParkBold;
}

Call it with

<h2 class='NationalParkBold' style='font-size: 400%;'>

Like This

or

<p class='NationalPark'>

You can also use it in paragraphs, but it doesn’t have the impact like it does with headers. This paragraph is not much different than normal san serif text.

wget

April 22nd, 2019

Occasionally I run across a site that has a bunch of PDFs that I want to read. This wget lets me download them for later reading.


wget -r -np -l 1 -A pdf http://example.com/pagewithpdf.html

If you want to use the downloaded website for local browsing, you will need to pass a few extra arguments to the command above.

wget -m -k -p https://example.com

Well Golly


Atheism Plus

Buy from Amazon