Axibase Time Series Database Use Cases

Time Zone Administration

Landscape with the Arrival of Aeneas before the City of Pallanteum by Claude Lorrain (c. 1625)

Contents

This workshop enumerates and explains organizations and agencies which maintain and organize international time zones as well as the methodology implemented for reasons of precision.
For information relating to improvements in local time-keeping accuracy, refer to Historical Time Keeping

Overview

Time zones were conceived to standardize the differences in time between geographical locations by reliable method. The idea of relative time began as a tool of navigation, but grew into a tool of administration, as technology allowed and globalization necessitated.

Modern time zones are standardized to Greenwich Mean Time (GMT) also called Coordinated Universal Time (UTC). Modern time keeping is so precise the Earth's slowing rotation is considered during calculation.1

Methodology

Apparent Solar Time

  • Solar day (24 hours) is derived via measurement of one complete solar cycle.
  • Used in conjunction with the sidereal day (23 hours 56 minutes), calculated from the location of the stars.
  • Problems:
    • Elliptical orbit of Earth around the sun.
    • Imperfect rotational axis; effects of latitude.
    • Difference in solar and sidereal day lengths.

Mean Solar Time

  • Day begins at 00:00 which corresponds to midnight, 12:00 is set at the zenith of the sun.
  • Mean solar day is calculated based on a one year solar cycle.
  • The length of the mean solar day is gradually increasing each year.

Problem Solving

Find apparent solar time (AST) at 8:00 a.m. MST on 21 July in Phoenix, AZ, which is located at 112° W longitude and 33.43° N latitude.

Parameters:

  • Phoenix does not observe DST.
  • 21 July is day 202.
  • Local Standard Time Meridian: LSTM = 15° * ΔTGMT
  • The equation of time: EoT = 9.87sin(2B) - 7.53cos(B) - 1.5sin(B) where, B = 360°/365 * (d - 81) and d is the number of days since the start of the year.
    • For these purposes, B = 119.342
  • The time correction factor: TC = 4(LSTM - Longitude) + EoT

Solution:

EoT = 9.87sin(288.384°) - 7.53cos(119.342°) - 1.5sin(119.342°)
= -6.05 minutes

LSTM = 15° * (112°/15°)
= 105°

TC = 4 minutes * (105° - 112°) - 6.05minutes
= -34.05 minutes

At 8:00 a.m. on 21 July, apparent solar time is 7:26 a.m.

Refer to Applications for additional calculations which relate to apparent solar time and local time.

Applications

Officiating Bodies

Despite international cooperation in a number of practices, different components of international time are still organized by different agencies.

  • The Internet Assigned Numbers Authority maintains the tz or zoneinfo database, which contains code and data for many representative locations around the globe. It is updated periodically to reflect changes made by political bodies to time zone boundaries, UTC offsets, and daylight-saving rules.33

  • Historical time zones are recorded from 1 June, 1970 (Unix epoch) for the entire planet, as well as other celestial bodies.

  • Pre-1970 time zones are recorded on a best-effort basis.

  • The International Telecommunications Union regulates the precise definition of UTC according to guideline Rec. ITU-R TF.460-6.

  • Current regulatory issues revolve around correcting discrepancies between International Atomic Time and Universal Coordinated Time.32

  • The Bureau International des Poids et Mesures coordinates time among roughly 400 atomic clocks throughout its 60 member countries to definitively establish a scientific standard time.

  • Established 20 May, 1875 to standardize units of measurement among disparate nations.

True Solar Time

As an observer travels longitudinally, that is, east to west or vice versa, true solar time is constantly changing. Because of the complications this introduces to calculation, large tracts of land are often given a derived mean time zone, as seen on the map below.

Listed below are various large cities, their central latitude and longitude, the UTC offset applied to calculate local time, and their populations.

City Latitude Longitude UTC Offset (hours) Population (million)
Berlin 51°30'35'' N 13°23'20'' E +1 3.58
Chicago 41°52'55'' N 87°41'05'' W -6 2.72
Kamchatka Peninsula 56°20' N 160° E +12 0.32
London 51°30'35'' N 0°7'39'' W 0 8.14
Los Angeles 34°3' N 118°15' W -8 4.0
Moscow 55°45'7'' N 37°37' E +3 11.9
New York 40°43'50'' N 70°0'21'' W -5 8.6
Omsk 54°59'32'' N 73°22' E +6 1.2
Rio de Janeiro 22°54'39'' S 43°11'47'' W -3 6.3
Saint Petersburg 59°57' N 30°18'31'' E +3 5.0
Tokyo 36°39'10'' N 139°41' E +9 9.3
Warsaw 52°14'13'' N 21°1' E +1 1.8

Source: Google Maps, 201833

Assumptions:

  • The Earth is a sphere.
  • The sun traverses this sphere in 23 hours 56 minutes and 4.1 seconds (23.933 hours for calculation).34
    • Each longitudinal degree represents ±4 minutes from the origin.

Procedure:

  • Calculate true solar time using SQL.
  • Derive margin of error of UTC offset.

Download the true-solar-time CSV file and parser configuration used to insert data into ATSD.

Calculation:

SELECT longitude.tags.city AS "City", longitude.value AS "Longitude", "offset".value AS "Offset (Hours)", abs(longitude.value*4) AS "True Offset (Minutes)", abs("offset".value*60) AS "UTC Offset (Minutes)", abs((longitude.value*4) - abs("offset".value*60)) AS "Margin of Error (Minutes)"
  FROM "longitude"
  JOIN "offset"
  WHERE longitude.tags.city != 'null'
ORDER BY "Margin of Error (Minutes)" ASC
City Longitude Offset (Hours) True Offset (Minutes) UTC Offset (Minutes) Margin of Error (Minutes)
London 0.0 0.0 0.0 0.0 0.0
Berlin 13.3 1.0 53.2 60.0 6.8
Los Angeles 118.3 8.0 473.2 480.0 6.8
Rio de Janeiro 43.2 3.0 172.8 180.0 7.2
Chicago 87.6 6.0 350.4 360.0 9.6
New York 70.0 5.0 280.0 300.0 20.0
Warsaw 21.0 1.0 84.0 60.0 24.0
Moscow 37.6 3.0 150.4 180.0 29.6
Tokyo 149.6 9.0 598.4 540.0 58.4
Saint Petersburg 30.3 3.0 121.2 180.0 58.8
Omsk 73.3 6.0 293.2 360.0 66.8
Kamchatka Peninsula 160.0 12.0 640.0 720.0 80.0

True Zenith Time

According to the principles of mean solar time, 12:00 corresponds to the sun's highest point in the sky during the day. Because time zones are regulated by a longitudinally oriented system, significant latitudinal distance is encapsulated within one time zone.

Because Earth orbits the sun on an elliptical path and rotates about an axis whose absolute angle is not 0°, the length of days differs throughout the year naturally.

Assumptions:

Procedure:

  • Calculate LSTM and EoT values to derive time correction factor.
  • Check answers using the formula for local solar time: LST = LT + TC/60

Calculation:

SELECT city AS "City", RADIANS(lat) AS "Latitude (rad)", RADIANS("long") AS "Longitude (rad)", lstm AS "LSTM", (4 * RADIANS("long") - lstm) + ((9.87 * sin(2 * DEGREES(360/365 * jan1))) - (7.53 * cos(DEGREES(360/365 * jan1))) - (1.5 * sin(DEGREES(360/365 * jan1)))) AS "1 Jan", (4 * RADIANS("long") - lstm) + ((9.87 * sin(2 * DEGREES(360/365 * may1))) - (7.53 * cos(DEGREES(360/365 * may1))) - (1.5 * sin(DEGREES(360/365 * may1)))) AS "1 May", (4 * RADIANS("long") - lstm) + ((9.87 * sin(2 * DEGREES(360/365 * sep1))) - (7.53 * cos(DEGREES(360/365 * sep1))) - (1.5 * sin(DEGREES(360/365 * sep1)))) AS "1 Sep"
  FROM
  (
  SELECT latitude.tags.city AS "city", latitude.value AS "lat", longitude.value AS "long", -80 AS "jan1", 40 AS "may1", 161 AS "sep1",  RADIANS(15) * "offset".value AS "lstm"
  FROM "latitude"
  JOIN "longitude"
  JOIN "offset"
  WHERE longitude.tags.city != 'null'
  )
ORDER BY city ASC
City Latitude (rad) Longitude (rad) LSTM 1 Jan 1 May 1 Sep
Berlin 0.899 0.232 0.262 5.6 0.5 3.1
Chicago 0.731 1.529 -1.571 12.6 -7.5 -3.9
Kamchatka Peninsula 0.983 2.793 3.1 13.0 7.9 4.3
London 0.899 0.000 0.000 4.9 -0.1 23.8
Los Angeles 0.595 2.065 -2.065 -15.3 -10.2 6.6
Moscow 0.970 0.656 0.785 6.8 10.7 21.9
New York 0.709 1.279 -1.222 1.1 -6.1 -2.4
Omsk 0.958 1.371 1.571 8.5 3.4 2.0
Rio de Janeiro -0.398 0.754 -0.785 8.7 -3.7 0.0
Saint Petersburg 1.045 0.529 0.785 6.3 1.2 -2.4
Tokyo 0.639 2.611 2.356 -13.0 -17.9 -14.3
Warsaw 0.913 0.367 0.262 -6.1 15.1 32.6

Margin of Error:

City 1 Jan Derived 1 Jan Recorded 1 Jan Error 1 May Derived 1 May Recorded 1 May Error 1 Sep Derived 1 Sep Recorded 1 Sep Error Overall Error
Berlin 12:07 12:09 0.27% 12:01 12:03 0.27% 12:03 12:06 0.43% 0.32%
Chicago 11:47 11:54 0.98% 11:52 11:47 0.70% 11:56 11:50 0.82% 0.83%
Kamchatka Peninsula 12:13 12:28 2.05% 12:08 12:17 1.24% 12:04 12:20 2.16% 1.82%
London 12:05 12:04 0.14% 12:00 11:57 0.42% 12:24 12:24 0.00% 0.19%
Los Angeles 11:55 11:56 0.14% 11:45 11:50 0.70% 11:50 11:52 0.27% 0.37%
Moscow 12:07 12:13 0.82% 12:11 12:16 0.68% 12:22 12:29 0.94% 0.81%
New York 12:01 11:59 0.27% 11:54 11:53 0.14% 11:57 11:55 0.27% 0.23%
Omsk 12:09 12:09 0.00% 12:03 12.03 0.00% 12:02 12:06 0.55% 0.18%
Rio de Janeiro 11:51 11:56 0.42% 11:56 11:49 0.99% 11:56 11:52 0.56% 0.66%
Saint Petersburg 12:06 12:02 0.55% 11:59 11:55 0.55% 11:58 11:58 0.00% 0.36%
Tokyo 11:51 11:44 0.99% 11:43 11:38 0.82% 11:46 11:41 0.42% 0.42%
Warsaw 11:54 11:54 0.00% 12:25 12:33 1.01% 12:33 12:36 0.43% 0.48%

Average Margin of Error^:
1 January: 0.48%
1 May: 0.52%
1 Sep: 0.57%
Overall: 0.52%
^Does not include Kamchatka Peninsula data.

Martian Time Zones

Mars, pictured below, is the fourth of eight planets in our solar system and among the most similar to Earth.35

  • Coordinated Mars Time (MTC) is a proposed Martian derivative of UTC along the prime meridian proposed by German astronomers Wilhelm Beer and Johann Heinrich Mädler in 1830.36

Comparative Statistics

Earth Mars
Angle of Rotation 23.5° 25.2°
Diameter 12,742 km 6,787 km
Length of Day 24 hours 24 hours 39 minutes (24.655 for calculations)
Orbital Period 365 days 687 days
Average Distance from the Sun 149.6 * 106 km (1 AU) 1.524 AU
Highest Point on Surface 6,263 m 24,000 m

Source: NASA Jet Propulsion Lab.

  • The Darian System, established by American aerospace engineer Thomas Gangale, is the most widely used Martian calendar.
  • Due to Mars' longer orbital path and period, the Martian calendar is 22 months long.

A sol is the duration of a solar day on Mars: 24 hours 39 minutes and 35.244 seconds.37
Adopted during the Viking Lander missions.

  • Each longitudinal degree represents ± 4.11 minutes from the origin.

Source: University of Texas, Richard Fitzpatrick.38

  • Mars' severe axis of rotation, and its asynchronous orbit create a distorted version of the Equation of Time function.
  • Longer orbital path as well as angle of rotation increases the amount of time by which solar days differentiate throughout the year.

The Martian Day

  • The diameter of Earth is roughly 187% that of Mars, but sidereal and solar days only differ by 2%.
    • Within our solar system, planetary days range from 10 (Jupiter) to 5832 (Venus) hours.
  • This similarity is explained by the culmination of billions of years, billions of events, and in the end: coincidence.
    • Mars and Earth are different sizes.
    • Mars and Earth have a different number of moons (Mars has two).39
    • Mars and Earth are dramatically different distances from the sun.

Mars Retrograde

Mars retrograde, visualized by position.
Source: NASA Mars Expedition.

  • Biennial event whereby the position of Mars in the night sky moves from east to west, caused by relative differences in planetary orbits.40
Feedback? Comments?