Time Zones and Countries

I needed a quick list of countries and time zones and I wanted to add it to my database so I could manipulate it easily for a new application we’re developing (details on that coming soon). I looked around but didn’t find what I wanted so after a bit of searching I took a few static select lists and copied them into Excel. I then imported the lists into my database using ColdFusion. In case they help anyone I’ve made the lists available here as CSV files.

Time Zones (.csv, 4 KB)

Countries (.csv, 4 KB)

<!--- get and read the CSV-TXT file --->
<cffile action="read" file="time_zones.csv" variable="csvfile">
<!--- loop through the CSV-TXT file on line breaks and insert into database --->
<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
    <cfquery name="importcsv" datasource="my_dsn">
         INSERT INTO time_zones (offset,name)
         VALUES ('#listgetAt('#index#',1, ',')#','#index#',2, ',')#')
   </cfquery>>
</cfloop>
</cffile>

The ColdFusion code assumes that you have setup a Datasource named “my_dsn” and that you have created a database with a table named “time_zones” that has three columns: time_zone_ID (set to automatically increment as a Primary Key), offset (with a FLOAT data type), and name (VARCHAR data type).

The code snippet reads in the CSV file and inserts it into the database, you should be able to do something very similar using the language of your choice. Feel free to download the .CSV files and play around with them or use them in your own projects. If you have any improvements to make to them, such as more information on things like daylight savings time or country codes) feel free to send them to us and we’ll make them available them here.

Kevin Hall

Kevin Hall

I am one of the owners of Infinite Web Design. I'll be glad to talk with you about who we are, what we do, or any of the topics we write about here.
Kevin Hall

Latest posts by Kevin Hall (see all)

One thought on “Time Zones and Countries

  1. thanks for the great list, do you know where I can get a list of counties or provinces for other countries like the United Kinkdom? I noticed it on the paypal site, but couldnt find out where they got that from

Comments are closed.