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: Connect to a MySQL database using Connector/.NET in C#

Try this:

How to: Create and insert SQL DATE or DATETIME objects using PHP

I was helping a student with a bit of project work the other day and had to create a properly formatted Date object from input provided by some dropdown menus, so in the spirit of only ever solving the same problem once, this is how you go about it… I’ll use MySQL for this example, but if the DATEs or DATETIMEs or whatever you’re using have a slightly different format in your DBMS of choice, then the principle’s the same to generate a compatible object.

MySQL Time Formats

The MySQL DATE type has the format YYYY-MM-DD, so for example today (or at least when I’m writing this post) would be 2011-08-19.

The MySQL DATETIME type has the format YYYY-MM-DD HH:ii:SS, so if it’s 9:36am and 53 seconds into the 19th of August 2011, that would be 2011-08-19 09:36:53.

The formatting of the numbers (i.e. the sequence of digits) is super important, but the dashes and colons are optional. This means that when you’re creating a PHP object to represent a DATE or DATETIME, you could create an object which contains 2011-08-19 or 20110819 etc. and MySQL would accept them as the exact same thing.

Constructing a suitable object in PHP

To create our PHP object, we’re going to use the PHP date and mktime functions. For this example I’m just going to create some variables which hold any date or time values, but in the real world you’d probably get them from a dropdown menu or calendar control.

Which when we then take a look at the database shows us:

DateTime Database Record

Result!

Automatically padding out numerical data with leading zeroes

If you need to work directly with numerical data (as opposed to strings), then you can use the following function (written by matrebatre over on the str_pad page) to pad out your data:

Done & dusted.

How To: Insert data into a MySQL DB using Connector/C++

I created a guide to using MySQL Connector/C++ for a class a while back which showed a not-too-pretty way to put data into a DB, and I created a second part using prepared statements, locking, rollbacks and all that other good stuff – then I never posted it! Well, no more ;)

MySQL Connector/C++ Guide - Part 2

Details: MySQL & Connector/C++ Introductory Guide – Part 2 of 2
Format: Powerpoint 2003 (Best readability in Powerpoint 2003 onwards, LibreOffice and OpenOffice)
Slide Count: 19
Link: MySQL-Connector-Guide-Part-2.zip

If you find anything that needs correcting feel free to let me know! Cheers!

How To: Work with MySQL and Connector/C++

I threw together a set of slides on the topic for a class which was presented today… Thought maybe they’ll be of use to someone else.

MySQL & Connector Guide

Details: MySQL & Connector/C++ Introductory Guide
Format: Powerpoint 2007 and OpenOffice Impress
Slide Count: 26
Link: MySQL-Connector-Guide-1.zip

If you find anything that needs correcting feel free to let me know and I’ll make any changes as required.

Also, there’ll be a second set of slides posted soon on using prepared statements, rollbacks and enforcing transactional integrity – isn’t life fun? =P

Update: Second part now available here.