Load timezone data by city and country into MySQL

If ever you needed to look-up timezone data in your MySQL database you know that you need to find a nice source for that data and load it into your database.

This is how I did it:

1. Download timezone information by cities from this site: http://citytimezones.info/cms/pending_requests.htm (this example uses the CSV format - download that file)
2. Unzip the file and note the location (full path) of the "cities.txt" file.
3. Use the following SQL to create a table to hold the data:

   CREATE TABLE `timezone_cities` (
     `city` varchar(100) NOT NULL,
     `timezone` varchar(250) NOT NULL,
     `country` varchar(45) NOT NULL,
     `latitude` double NOT NULL,
     `longitude` double NOT NULL,
     `timezone_name` varchar(200) NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

4. Adapt and run the following SQL statement in MySQL to load the data:

   LOAD DATA LOCAL INFILE '/Users/nerd/Downloads/cities_csv/cities.txt'
   INTO TABLE timezone_cities
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   LINES TERMINATED BY '\r\n';
   

Make sure that when you run the LOAD DATA query, that there are no errors or warnings.
Note that I did not optimize the design of the table (field sizes) to match the data ( DIY )