PHP ZipCode to TimeZone

I have been building a database of customers to call and found one slight issue, I needed to be able to workout which timezone each of the contacts are in so I only call when its suitable for them!

I started off by using one of the online ZipCode to TimeZone converters and manually adding the data to the existing database, but soon ran into difficulties because the websites limit the number of requests that could be run (Its cookie based so while I could probably have worked round it I needed a better solution). I also looked at using the telephone area code in the database and the various online converters which provide the TimeZone based on it, again after a number of requests the pages stopped working.

Having to cut and paste the Zipcode between the pages was also very painful, I wanted something I could just program and run. There are a number of API’s out there that will allow you to run requests against web pages and receive XML data back. These tended to return data which needed more massaging and required conversion of XML structures to allow the database to be updated.

I wanted to have a database of ZipCodes which had the Timezones included, I found one at the following link. I did find others but this one appeared to have been updated more recently. I started by importing the sql into a new MySQL database called ziptotz. On reviewing the data I found that the TimeZone was in a format such as ‘America/Alaska’. I needed to take this data and work out the time offset.

My solution was to simply add a new field to the end of each row which would hold the offset from GMT, I could have made it simpler by determining the offset of my current timezone from GMT and subtracting it from the result but the data would then be location specific so I decided offset from GMT would work just fine.

This is the script I used to add the new values.


// connect to the server
$con = mysql_connect("my_host","my_user","my_pwd");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
// select the database
mysql_select_db("ziptotz",$con);
// list the timezones supported by PHP for the US only
$timeZones = DateTimeZone::listIdentifiers(DateTimeZone::PER_COUNTRY, 'US');
// loop through the returned array
foreach ($timeZones as $key => $zoneName ) {
// create a timezone object
$tz = new DateTimeZone($zoneName);
// create a new datetime object using the timezone object time is current
$dateTime = new DateTime("now",$tz);
// create the offset char string
$timeoffset = date_format($dateTime, 'P');
// add the offset to the list of zipcodes
$query = "UPDATE timezonebyzipcode SET offset_gmt = '" .$timeoffset ."' WHERE timezone = '" .$zoneName ."'";
$result = mysql_query($query);
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}
}
// close the server connection
mysql_close($con);

Now I have a full list of ZipCodes with the gmt offset stored in a database table. Now I can use the data in this new table to add a new field to the contact database which will show the time offset allowing me to know when I should be able to call without waking them at 5AM in the morning.

Have fun..

Chris..

Article Global Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Eli Pets

This entry was posted in Marketing, MySql, PHP, PHP Programming by Chris Hird. Bookmark the permalink.

About Chris Hird

Chris Hird President and Owner Shield Advanced Solutions. Hird first worked with high availability at IBM Havant in the United Kingdom in 1989, and he was responsible for the technical interface with the developers of HA products and setting up a support structure in the UK to support the IBM customers. He left IBM to set up Shield Software Services in 1993, which was an IBM business partner and a MiMiX reseller, and in 1997 he moved to Canada and launched Shield Advanced Solutions, which provides tools and utilities aimed mainly at supporting HA Environments. Programs in C and PHP on the i5.

Leave a Reply