Month: December 2019

Perl Date and Concatenation

I’m working with a legacy script that needs the date in YYYY,MM,DD format and the original programmer went through some contortions to get MySQL to calculate the date and report it out. It seemed to me that perl should be able to do that easily. In fact, it can.


my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
my $perl_month = $mon + 1;
my $perl_year = $year + 1900;
my $perl_today = "$perl_year,$perl_month,$mday";
my $perl_today2 = ($year + 1900) . "," . ($mon + 1) . "," . $mday;

if ($DEBUG) { print STDOUT "\n Perl thinks today is: $perl_today, $perl_today2  \n"; }

Perl thinks today is: 2019,12,13, 2019,12,13

Perl can do the arithmetic, but you need to put the calculations in parentheses. If you don’t it gets confused and outputs 2020,13. Note that localtime() gives the year as an offset from 1900 and the month starting with January as 0—much like array notation.

Fixing a MySQL table

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

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.