I need a mapping list of cities to timezones- best way to get it?

Question

Hello all- I have a list of cities, states/provinces, and countries and I need to find their respective timezones easily. Specifically, I need to know which Windows TimeZone they map to. So far this has been a difficult process, because there's no easy way to pass a city to something and get a timezone back (a timezone being an offset and whether or not the timezone supports Daylight Savings Time).

The current flow is to use http://www.batchgeocode.com to get the latitude/longitude of the city (which really calls a Yahoo service) and then call http://www.EarthTools.org to get a timezone letter, which maps to an offset. The problem is the yahoo service will sometimes return a bad longitude/latitude, and earthtools.org doesn't know a lot about DST info so mapping to the correct TimeZone Id is a tedious manual process.

I can't be the first person who has done this- does anybody know of a better way, or some list out there which has the info I need?

Thanks.

Answer 1

Interesting question! These other three SO posts (41504, 55901 and 237023) mention a couple of leads. There's the Olson database (see the Wikipedia entry for description), but it looks more algorithmic/programmatic (e.g. if you know a county name, there's a rule for it for certain counties in Indiana).

I tried looking at EarthTools but it failed for St.Pierre/Miquelon, one of the good test cases (-3:00 rather the -3:30 for nearby Newfoundland which is what EarthTools says).

Some promising options: worldtimeengine.com has a free interactive version and an API which appears to be inexpensive (5000 queries per British pound), but the API only covers lat,long queries as inputs (not location as input, which is something the interactive version has).

Best lead: I'd look at geonames.org which looks like it has a nice rich API that you could use, and they have both a free (slow) version, and a commercial version for better performance.

p.s. here are some good test cases (from worldtimezone.com and Wikipedia's list of timezones, the map of time zones is also illustrative):

• Caracas, Venezuela (-4:30)
• Gander, Newfoundland (-3:30)
• Tehran, Iran (+3:30)
• Kabul, Afghanistan (+4:30)
• Kathmandu, Nepal (+5:45)
• Yangon, Myanmar (+6:30)
• Chatham islands, New Zealand (+12:45)
• Easter Island (-6)
• St. Pierre and Miquelon (-3)
• Stanley, Falkland Islands (-4)
• Evansville, Indiana (-6=Central)
• Ontario, Oregon (-7=Mountain)