I have a legacy application i'm going to need to supplement some data with. Currently, we have a DB table storing US (and its territories) zip codes, along with a GMT Offset, and a flag showing if that Zipcode uses daylight savings time. This was downloaded from some free provider, which I can't find the source to right now.
I now need to supplement this table with the full Olson name (e.g. America/New York
) of each zipcode because that seems to be the only good way to convert a given date/time stored in the database as local to that purchaser into a UTC aware datetime
object.
Here's a look at the table:
zip state city lat lon gmt dst
00605 PR AGUADILLA 18.4372 -67.1593 -4 f
02830 RI HARRISVILLE 41.9782 -71.7679 -5 t
99503 AK ANCHORAGE 61.1895 -149.874 -9 t
In another related table Purchases
, I have a postres
timestamp without tz
column, which currently contains something like 2014-05-27T15:54:26
, which represents some time local a purchase was made at that zip code. (ignore the stupidity of stripping out timezone information when saving these localized timestamps to the database)
The big ask is:
How can I create a normalized UTC time
from that timestamp
string for each zipcode in the zipcode
table? This would assume that the timestamp was written to the DB as local to each of the example rows in the zipcode
table.
For example, manually looking up the Olson timezone names for each item in the example table, I come up with the following:
>>> timestring = '2014-05-27T15:54:26'
>>> dt_naive = datetime.strptime(timestring, '%Y-%m-%dT%H:%M:%S')
>>> # First example - Puerto Rico (no DST since 1945)
>>> print pytz.utc.normalize(pytz.timezone('America/Puerto_Rico').localize(dt_naive))
2014-05-27 19:54:26+00:00
# Second example - Road Island (At that timestamp, UTC Offset was same as PR because of DST)
>>> print pytz.utc.normalize(pytz.timezone('US/Eastern').localize(dt_naive))
>>> 2014-05-27 19:54:26+00:00
# Third Example - Anchorage, AK (AKDT at timestamp)
>>> print pytz.utc.normalize(pytz.timezone('America/Anchorage').localize(dt_naive))
2014-05-27 23:54:26+00:00
I've seen several commercial products selling a zipcode database which can give me a zipcode -> timezone lookup. However, they seem to only give me "EST" for a given timezone. So, I thought I could map the list of possible timezones for US timezones (including territories) to an olson name for each. That might look something like this:
zipcode_olson_lookup = {
('PR', 'f', 'AST'): 'America/Puerto_Rico',
('AK', 'f', 'AKDT',): 'America/Anchorage',
('AK', 't', 'AKT',): 'America/Anchorage',
...
}
Any suggestions are greatly welcome!
See Question&Answers more detail:
os