MySQL Datetime

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.

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

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

Create MariaDB backup

I recently ran across a table that was not able to be read and it occurred to me that I should be archiving my database on a regular basis, rather than relying on just the Linode backups that are done nightly.

This command will do it for you:

mysqldump -u root --all-databases | gzip > backup.sql.gz

Search Tips

Search engine behaviour has changed over the years in ways that make it more difficult for me to get results that I am looking for. For my vocabulary sites I often want to get a word and a specific context. For example, if I want to find the word gregarious used in the same context as buffoon, I used to be able to put both words in the search box and it would find pages with both words. Not any more. When using Google, you can force it to find both words in the text of the page by using:
allintext:gregarious buffoon

Another one that I can make use of is:
gregarious AROUND (4) buffoon

Unfortunately, these don’t work in DuckDuckGo.

There are a bunch more at the ahrefs blog