Time database

Mailing list

http://groups.yahoo.com/groups/tk-db

Software

? http://www.sqlite.org/

Comparison of properties - tkdb vs IANA

Topic	Time keeping database (tkdb)	IANA time zone database (tzdb)
Terminology	Exactly one term for one kind of object	Terms are used interchangeably (region = zone) Terms are used ambiguously (zone for IANA zones and real world zones)
Area/Continent	Strictly defined boundaries for all Continents The East and the West boundary for America is each defined by exactly one meridian, allowing depiction on maps having America left-hand or right-hand, without cutting any continent apart from Antarctica. Western boundary of America is the Umnak meridian in the Bering Street. The Bering Strait as location for a reference meridian was already proposed at the International Meridian Conference in 1884, to avoid cutting continents.	The corresponding IANA concept is named "area" and the extensions of these areas are poorly defined. some zones that are located in an ocean are filed under an ocean (Indian/.., Atlantic/...), some under the nearest continent names of zone data source files do sometimes agree with area names, and sometimes they don't and one cannot easily find the data the boundaries of Antarctica do not adhere to the definition of the Antarctic Treaty
Area/Jurisdiction	ISO 3166-1 alpha-3 codes for jurisdiction.	ISO 3166-1 alpha-2 codes for countries. Some of the codes have been re-assigned, e.g. CS can refer to Czechoslovakia or Serbia and Montenegro.
Area/Jurisdiction	Support for former jurisdictions at least back to 1946-01-01, the first full year where the UN existed.	No support for former countries. E.g. no way to get data for ISO 3166 alpha-2 code DD (East Germany)
Area/Jurisdiction	At any point in time (at least after 1946-01-01) the land surface is associated to a jurisdiction.	Some areas are not associated to a country, e.g. the land of the ISO 3166 country "Bouvet Island" is not associated to any country.
Area/Sector	The smallest territorial unit is named "sector".	The smallest territorial unit is named "zone", but sometimes also referred to as region. The basic term "zone" is ambiguous, as there are also real world time zones.
Area/Sector	A sector is defined by boundaries.	A "zone" is defined as the territory around a reference location that had the same time since the cutoff point. Therefore, when time observation changes in the zone, or the cutoff point would be moved, the boundaries of the zone may change.
Area/Sector	For any point in time, except for war time, the legal time in all territory of a sector is the same.	For any point in time after 1970-01-01T00:00:00 clocks in a IANA zone have agreed.
Area/Sector	The sectors cover the whole surface of the earth	Some areas are not covered, e.g. no IANA zone exists the ISO 3166 country "Bouvet Island"
Area/Sector	Each sector has exactly one SectorID.	Some zones have multiple IDs via links. These links can represent former IDs of the zone due to spelling change for the reference location represent former IDs of the zone due to reference location change (Tel Aviv -> Jerusalem) represent IDs of former zones (Vaduz links to Zurich)
Area/Sector	A SectorID only contains alphanumeric characters, letters upper case.	A zone ID contains ASCII letters and / and can contain -._
Area/Sector	New sectors are created only by splitting and deprecating old sectors, so a user can know if an assignment of an ID to an object needs a check for correctness.	After a split, a zone retains the ID of the old zone. That means: a user can link an object to a tzid, but get's no notification if later the object is located in a zone that has been split off.
Area/Sector	Relations between deprecated sectors and the sectors that have been created out of them are published.	No split history is explicitly published.
Area/Sector-Jurisdiction	At any point in time no jurisdiction boundary is located inside a sector.	Zones can be located in different former countries, e.g. Europe/Berlin is located in East Germany (DD) and West Germany (DE) prior to 1990.
Coverage	Record all legal time	no time for the Norwegian Bouvet Island, while Norwegian government has defined it, limited support for pre-1970 data
ZoneId	For any point in time, for any country, a time zone acronym used for a sector that is located in that country refers to exactly one offset from a base time.	For some points in time during DST observance in Australia, the acronyms EST and CST for time records in Australia can refer to different offsets from UTC, depending on whether a zone observes DST or not. That means for a given point in time during summer the offset from UTC and therefore UTC itself cannot be derived from the local time representation.
DataFormat	Publication in SQL (?) as xCal/iCal (?) IANA specific format, as far as that format supports the features of the tkdb.	Publication in IANA specific format.

Tables

IdSystem

Name	Regex	Description
ContinentId	/[4-9]/	Not used: 0 - to avoid misreadings as O 1 and 2 - to avoid that a 4-digit SectorId could look like a year 3 - to have room for extensions, e.g. other planets, or private use area. similar to first digit of country calling code, https://commons.wikimedia.org/wiki/File:CallingCodesWorld-Labeled.svg
JurisdictionId	/[A-Z]{3}/	The ID for these should /not/ be the ISO 3166-1 alpha-2 codes, as they have been re-assigned, e.g. CS stands for 1) Czechoslovakia and 2) Serbia and Montenegro. There exists no ISO codes for countries that did not exist after the first ISO 3166 publication. It is proposed to use the ISO 3166-1 alpha-3 codes and where needed the Private Use Area of that set. These IDs could be used to relate source documents like laws and newspaper articles to the database.
RegionId
SectorId	/[4-9][A-Z0-9]{2,}/	The first digit is the ContinentId. Further restriction likely. There is no reference to a country (IANA zone sometimes to, e.g. those for Argentina) There should be a Private Use Area in the SectorId space, to allow people that like to do different sectorization to create non-clashing SectorIds.
ZoneId	/[A-Z]{4,}/	Maybe using acronyms similar to the Internationalized time point unique time zone abbreviations. Further restriction likely.
LawId

Meridian

Name	ReferenceMeridian	ReferenceDistance	IRM coor
IRM	0		0
Umnak	IRM	168.75° West	-168.75
Coetivy	IRM	56.25° East	56.25
Florence	IRM	11.25° East	11.25
Rocas	IRM	33.75° West	-33.75
Ferro	Paris	20° West	~17° 39' 46″ West
Paris			2.337229 (2° 20′ 14.025″ East)

MeridianDistance

Descriptive table, not needed for definition.

East	West	Degree	Hour	Day
Umnak	Coetivy	135	9	3/8
Coetivy	Rocas	90	6	2/8
Rocas	Umnak	135	9	3/8

Area

Except for zone, alphabetical sorting leads to decreasing size.

Name	Boundary	Description
Continent	Fixed	All surface of the earth belongs to exactly one continent.
District?
Jurisdiction	Not fixed	A jurisdiction is a political entity. The concept is similar to the concept of country in ISO 3166-1. But the term jurisdiction is preferred: to have the initial letter different from Continent
Region ?
Sector	Fixed	All land surface belongs to exactly one sector. A sector is located in exactly one Continent has exactly one SectorId is not divided by jurisdiction boundaries since 1946-01-01T00:00:00, except for time of war, when the boundaries between jurisdictions moved.
Ward?	Not fixed	Part of a zone, defined for zones that split during DST.
Zone	Not fixed	Real world time zone, e.g. Mountain Time Zone. For a given point in time located in one jurisdiction? That could contradict zones like Central European Time if there is no jurisdiction "Europe".

Boundary

Canton

Id	Name	East meridian	N/S
4	North America	Rocas	N
5	South America	Rocas	S
6	Oceania	Umnak	S
7	Asia	Umnak	N
8	Europe	Coetivy	N
9	Africa	Coetivy	S

Continent

They allow grouping of sectors and delegation of SectorId creation.

Id	Name	max E/W	max E/W	Description	IdReason
4	Antarctica	360°	24h	As defined by Antarctic Treaty.	4 is assigned to have this pole centered continent at the edge of the ContinentId space.
5	America	135°	9h	The East and West boundaries are straight lines, i.e. Greenland and Umnak are divided. This allows maps having America to the right or to the left, without cutting any continent except for Antarctica.	5 is chosen to have America apart from the "old world"
6	Oceania	135°	9h		6 is chosen since Oceania borders America (5). It coincides with the first digit of the country calling codes predominantly used in that area. As the former two areas, Oceania has few land boundaries with other continents.
7	Africa	90°	6h
8	Asia				8 coincides with the first digit of some country calling codes in East Asia. ASEAN Day is 08-08 (8 August).
9	Europe	225°	15h	Includes territory of former Soviet Union, Cyprus (island), Malta, Turkey.	9 is the first digit of calling codes for Turkey, Azerbaijan, Tajikistan, Turkmenistan, Georgia, Kyrgyzstan, Uzbekistan.

ContinentBoundary

Many common maps have the Chukotka Peninsula to the right hand, using the Umnak meridian (Bering Strait) as right-hand map limit. Except for Antarctica no continent shall be divided by the Umnak meridian, to avoid that a continent is depicted in two parts on such a map. Additionally no continent, except Antarctica, shall be divided on a map having America to the right hand.

168.75° West - Umnak meridian
56.25° East (135° West of Umnak [ (180-168.75) + (180-56.25) = 11.25+123.75 ], i.e. 9h West of Umnak, or 3/8 West, or 5/8 East)
33.75° West (135° East of Umnak, i.e. 9h East of Umnak, or 3/8 East, or 5/8 West of Umnak)

Id	Name	LowerId	HigherId	Description
45	AntarcticaAmerica	4	5	60° South
46	AntarcticaOceania	4	6	60° South
47	AntarcticaAfrica	4	7	60° South
56	AmericaOceania	5	6	168.75° West (Umnak meridian)
57	AfricaAmerica	5	7	33.75° West
58	AmericaAsia	5	8	168.75° West (Umnak meridian)
59	AmericaEurope	5	9	33.75° West up to the North Pole, then South on 168.75° West (Umnak meridian)
67	AfricaOceania	6	7	56.25° East
68	AsiaOceania	6	8	0°
78	AfricaAsia	7	8	From the north follow the border of Egypt through the Red Sea. In the Gulf of Aden go to the West until 56.25° East, then go South to the Equator.
79	AfricaEurope	7	9	Line in the Mediterranean, South of Malta, West to the Strait of Gibraltar 36° North.
89	AsiaEurope	8	9	From America follows the southern border of former Soviet Union, at the border of Turkey follow the southern border of Turkey to the Mediterranean. Passing between Cyprus and Syria. Assignment of all of Ex-Soviet Union to Europe as for ITU region 1 http://life.itu.ch/radioclub/rr/art05.htm#Reg

Jurisdiction

Sector

Law

LawToJurisdiction

n:m

SectorToWard

WardToZone

SectorToIanaZone

Publish relations between sectors and IANA zones at least for some IANA releases.

n:1 (for one IANA release)