Useful SQL Commands

I recently needed to take a database with separate date and time columns, convert that combined date-time data into Unix time format (i.e. seconds since the epoch [1st Jan 1970]), and add a new column to the database with that data. It took me a fair bit of googling to get it all working, so here’s my notes.

Sorry this isn’t really in an article format – but I need this written down and available for the future… Also, I wrote a small Java app to read the separate date and time text files and spit out a timezone-correct unixtime text file, which I then added to a spreadsheet containing just the the primary key rows and exported it as tab-separated values suitable for MySQL (or really, MariaDB in this case) import.

Show the table structure for the ‘accidents’ table

Count unique values in the UNIX_TIME column in the accidents table

Find duplicate values in a column (cannot make column a primary key if there are dupes!)

Make a column be the primary key for a table

Drop the UNIX_TIME column

Add a new column UNIX_TIME to the accidents table after the last column (STAT_DIV_NAME)

Add an empty column with no default value

Create a temporary table in memory with a single column called UNIX_TIME which is of type BIGINT

Import from local CSV file

I created a tab-separated CSV file which had just the ACCIDENT_NO as primary key and the UNIX_TIME field. We can then import that data into the temptable with:

Load all the single-line data values fromthe UNIX_TIME.txt file into the UNIX_TIME column of our temp table

Merge the temptable with just the ACCIDENT_NO and UNIX_TIME into the accidents table using ACCIDENT_NO as the primary key

So in this particular instance where I had a partial update, I was missing 13,375 UNIX_TIME values!

Make sure this worked by setting all the UNIX_TIME values in the accidents table to zero, and then re-importing

Now finally, we can drop the in-memory temptable!

Export the database

To export the database we can use the mysqldump utility (this is NOT within the MySQL command line!):

Then to import this dump, let’s say we don’t have a govhack2015 database

Followed by (in the MySQL command line):

It all works! =D

How to stop a MySQL action which is taking forever… (like when I bodge a merge) – in a different MySQL commandline interface

How To: Simply Convert A Directory of FLAC files to MP3 with FFMPEG in Linux

Stick the following in a file called flac2mp3 or such, chmod +x it, then put it in somewhere like /usr/local/bin

To use it, simply go to your folder of .flac files and run flac2mp3, then perhaps rm *.flac to get rid of the (massive) flac files you don’t want anymore.