lat/lon to time offset database

[Guy Harris]

On Feb 3, 2011, at 10:33 AM, Larry Ober wrote:

> I don't really need the geo political information like country or city. 
> However it seems like tz is structured continent/country/city/... 

To quote the tz-link.htm file in the tzcode package:

	Each location in the database represents a national region where all clocks keeping local time have agreed since 1970. Locations are identified by continent or ocean and then by the name of the location, which is typically the largest city within the region. For example, America/New_York represents most of the US eastern time zone; America/Phoenix represents most of Arizona, which uses mountain time without daylight saving time (DST); America/Detroit represents most of Michigan, which uses eastern time but with different DST rules in 1975; and other entries represent smaller regions like Starke County, Indiana, which switched from central to eastern time in 1991 and switched back in 2006. 

> Now maybe I already have what I need but I just don't understand it. I have a 
> copy of timezone.csv that seems to include data from country code to city etc. 

If I search for timezone.csv on Google, I get hits for at least two different data sets:

	http://code.google.com/p/x-wrt/source/browse/trunk/package/webif/files/usr/lib/webif/timezones.csv?r=2936

which doesn't seem to include any coordinate information, and

	http://msdn.microsoft.com/en-us/library/aa458853.aspx

which says the timezone.csv file "contains the Timezone Index, GMT Differential, and Description for each time zone".

Where did you get that timezone.csv from?

> This seems to point to what looks like coordinate data that I don't understand.
> 
> An example for America/New York seems to point to an area 115. There then seem 
> to be multiple 115 entries like the following;
> 
> 115, 3907893600, -18000, EST, 0
> 115, 3919388400, -14400, EDT, 1
> 
> My interpretation is that 115 is the area, the next column should be some sort 
> of latitude, next seems to be called an offset in other contexts, next is the 
> used name of the zone followed by a true/false bit.

*My* guess is that 115 is some representation of an area, 3907893600 is some representation of a time when the US Eastern time zone switches to Standard time, -18000 is the offset in seconds from UTC to use for the time zone after that time, "EST" is the abbreviation to use for the time zone after that time, and 0 is a "Daylight Savings Time is not in effect" indication.

I.e., if you sort all the entries for a given area by the second field, it's a list of standard <-> {daylight savings, summer} time transitions, with each entry saying when the transition occurs, what the offset from GMT in seconds is after that transition, what the zone abbreviation is after that transition, and whether you're in standard or {summer, daylight savings} time after that transition.