Some Preliminaries

Before we talk about datetimes and intervals, there is some necessary background to go through. We provide most of this information mainly for reference purposes, though. If you decide not to read it thoroughly right now, you'll still understand most of what follows. However, we will be referring to these concepts throughout this chapter.

[Obscure Rule] applies for the rest of this section.

The Gregorian Calendar

The SQL Standard says that all dates must be valid "according to the Gregorian calendar". Most people know the main rules:

• Thirty days hath September, April, June and November, all the rest have thirty-one, except (the rhyme scheme starts to fail here) February which has 28 days, or 29 in a leap year.
• A leap year occurs every four years.

These first two rules are the rules of the Julian Calendar. Pope Gregory XIII added this exception in 1582:

• A leap year does not occur at the end of a century, except every 400 years. (That is: 1700, 1800, 1900, 2100, 2200 and 2300 are not leap years.)

In a majority of practical situations it's only necessary to know what the Gregorian calendar is and that SQL enforces it. A minority of practical cases, however, involve historians or astronomers. For these cases, let's clear out the wrong ideas about the calendar rules and about the Julian-to-Gregorian transition.

The Julian calendar took effect starting January 1, 45 BC. (It is a nice coincidence that January 1 was the year-start in 45 BC and in our time; we ignore the variations that happened between then and now.) There was some confusion and fiddling until 4 AD (which was not a leap year) but after that, the first two rules held firmly: a leap year every 4 years, with the average "Julian year" being 365.25000 days long. (We now consider that the correct period for a year is 365.24220 days, decreasing by about 0.5 seconds per century. The difference, 000.00780 days, is statable as 3 days every 400 years.)

The result of this, though, meant that by the 1500s, the spring equinox was on March 11th. This violated Church teaching, particularly the finding of the 4th-century Council of Nicaea, which made clear that the spring equinox is March 21st. To solve the problem, Pope Gregory XIII had to do two things: shift the calendar forward 10 days and change the rules, so that there would no longer be an overestimate of 3 days every 400 years. He therefore decreed that the day after Thursday, October 4th, 1582 should be Friday, October 15th, 1582. The decree took effect immediately in the Papal States and Iberia, after a short delay in France, by 1700 in most German Protestant states and Scandinavia (though Sweden went back and forth). England held out until 1752 (by which time the discrepancy was 11 days, from September 3rd to September 14th). Japan went Gregorian in 1873, with the proviso that Year #1 is based on the Emperor's reign rather than Christ's birth. China changed in 1911, Russia in 1918 and Greece in 1923. Even among Moslem countries, the only non-fully-gregorianized significant holdout, there has been a breakaway: Turkey, in 1927.

As we said earlier, the switch to the Gregorian calendar doesn't affect most of us — but it has caused some problems for groups like historians and astronomers who use SQL.

The first problem is that SQL allows dates like DATE '1582-10-14' — even though, according to Pope Gregory, there was no such date. Also, any Gregorian date before October 3rd, 1582 is what the Oxford Concise Dictionary calls "Prolepsis: ... representation of thing as existing before it actually does or did so". (When Americans observed Washington's birthday on February 22nd, they were proleptic: he was born on February 11 OS, where the initials "OS" stand for "Old Style" i.e. Julian.) Going the other way, into the future, there will certainly have to be more tweaking, since the Pope's rules do not remove all drift.

The second problem is that, although it serves many purposes well, the Gregorian calendar is inevitably non-decimal. It would be simpler to begin with a fixed moment far in the past — noon on January 1st, 4713 BC for example — and count how many million days have elapsed since then, with no regard for higher units or any calendar rules. This is the system of "Julian days". Since a Julian day is expressible as a DECIMAL, there is no need for a separate data type for such values. Some ephemerides tables use Julian days, so if your project involves astronomy look for a DBMS that can convert a Julian day to a (proleptic Gregorian) DATE. Standard SQL DBMSs can't because they may only allow for dates starting with 0001 AD.

Leap Seconds

The earth's revolutions are getting shorter: it goes round the sun about 0.5 seconds faster than it did in 1900. We point this out for the sake of people who define "1 year" as "1 earth revolution period" — that kind of year is getting shorter, but the other kind, the "civil year", isn't. Here's why.

The earth's rotations are getting longer: it turns on its axis about 0.04 seconds slower than it did in 1900. A bit of the slowdown is due to tidal friction but mostly we're looking at an irregular and unpredictable fluctuation — indeed, for all we know, the rotation may get faster in future. We point this out for the sake of people who define "1 day" as "the average period between two sunrises" which is closely linked to the earth's rotation period. You can keep that definition, but you should see that such a shifty period cannot be the standard in a precise measurement system.

The resonance of a cesium-133 atom is getting neither shorter nor longer. Its electrons change spin (relative to the nucleus) at a constant frequency. So the International System of Units bases its definition of a "second" on a cesium clock, thus: "the duration of 9,192,631,770 periods of the radiation corresponding to [the shift between parallel and anti-parallel electron spin] of the caesium-133 atom". The official second is this atomic second, and since 1972 we have defined a day as 60*60*24 atomic seconds.

At one instant, the standard day was the same as the day-derived-from-rotation. But since the latter fluctuates, the two figures won't stay in synch. Yet we must synch them, else the number of days in a year would change fractionally with each revolution. The solution is: when the atomic-second time gains/loses relative to the from-rotation time, add/drop 1 or 2 seconds in the last second of the last day of a month. In practice it has always been necessary to add, and the change has always been on June 30th or December 31st. Since we are adding to the year, the term leap second is good by analogy.

The day-derived-from-rotation time is known as "Universal Time 1" (UT1); corrected for polar wobble, it is used for celestial navigation. The day-based-purely-on-atomic-clock time is known as "International Atomic Time" (TAI); it represents the consensus of several cesium clocks as monitored by a standards bureau in France. The atomic-but-synched-with-UT1-by-leap-seconds time is "Co-ordinated Universal Time" (UTC). It is this last time — UTC — which matters for time signals, for SQL and for us. Do not confuse UTC with the old standard "Greenwich Mean Time" (GMT); GMT was a variant of UT1 that used a different method to correct for fluctuations. Beware of two prevalent but false opinions: that years are not getting longer (they are), or that UTC is a renaming of GMT (it is not). The distinctions are tiny, but any program which uses leap seconds or fractional seconds is getting into magnitudes which are smaller than those tiny distinctions.

Knowing what leap seconds are, we can move at last to their use in SQL:

• First, the Standard requires a DBMS to extend the range of seconds-field values to "less than 62" (rather than "less than 60") and thus account for up to 2 positive leap seconds. (There is a GOTCHA here: leap seconds should always be for the last minute of a day, as in TIME '23:59:60', but the Standard allows erroneous values like TIME '12:34:60'.)
• Second, because of leap seconds, it isn't possible to tell whether TIME '23:59:58' is two seconds before midnight, one second before midnight (leap seconds can be negative, though it has never happened) or as much as four seconds before midnight — the information is simply not present in the syntax of a TIME expression, nor derivable from any Table. Thus, arithmetic with carrying has uncertainty. Not surprisingly, the SQL Standard states that any expressions which involve leap-seconded TIMEs will show implementation-defined results.

Time Zones

In 330 BC, a lunar eclipse was seen at Arbela around 3 AM and in Carthage around midnight. The ancient Greeks knew how eclipses worked so this was one proof that the earth is round (their other proofs were that the sun gets higher in the sky as we travel south and that we can still see ships' masts after their hulls disappear below the horizon). In our terms, Aristotle and Company were seeing that our anchor point of midnight — the halfway point between dusk and dawn — must change with longitude.

Nowadays, we mark the world off into time zones, with one time zone equal to about 15 degrees of longitude. Time zones are political divisions that allow us to use the convention that all locations in a time zone have the same time, known as local time. Thus, although times and timestamps are supposed to represent an absolute time of day (times) and an absolute time of a specific day (timestamps), they can have ambiguous meanings when an SQL-environment spans multiple time zones. The SQL Standard tries to cater both to users who have only local dealings and thus care only about local time, and to users who operate across time zones. It does this by providing a <time zone interval> option for time and timestamp values: a value without a <time zone interval> (e.g.: a TIME or TIMESTAMP <data type>) may represent local time or UTC time, while a value with a <time zone interval> (e.g.: a TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE <data type>) always represents the UTC time. Unless your SQL-environment spans multiple time zones and you have a need for "real time" database operations, the entire matter of time zones probably won't concern you. In that case, be sure to define your time and timestamp fields with the TIME and TIMESTAMP <data type>s; don't use the TIME WITH TIME ZONE or the TIMESTAMP WITH TIME ZONE <data type>s at all. If, however, "real time" operations are vital, you may want to define time and timestamp fields with the TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE <data type>s.

<time zone interval>

The required syntax for a <time zone interval> is:

 ::=
{+ | -} HH:MM

A <time zone interval> specifies a time or timestamp value's time zone offset from UTC. It has a <data type> of INTERVAL HOUR TO MINUTE. HH is 2 digits (ranging from 0 to 13) representing the number of hours (called TIMEZONE_HOURs) in the time zone offset and MM is 2 digits (ranging from 0 to 59) representing the number of additional minutes (called TIMEZONE_MINUTEs) in the time zone offset. For example, this represents a <time zone interval> of 3 hours:

+3:00

A <time zone interval>'s mandatory sign — either + (plus) or - (minus) — indicates whether the time zone offset is added to, or subtracted from, the UTC time to obtain the local time. The valid range of <time zone interval>s is from -12:59 to +13:00. Any operation that attempts to specify a <time zone interval> that is not within this range will fail: your DBMS will return the SQLSTATE error 22009 "data exception-invalid time zone displacement value".

A time or timestamp value that doesn't include a <time zone interval> represents a time in the SQL-session's current default time zone, that is, it represents a local time. A time or timestamp value that does include a <time zone interval> represents a time in the specified time zone.

If you want to restrict your code to Core SQL, don't use <time zone interval>s.

Time Zone Example

Time zones start at zero longitude (the Prime Meridian), which goes through Greenwich, Britain. The time zones West Of Greenwich ("Wogs") are behind UTC because the earth rotates from west to east. Therefore, when it's 12:00 UTC it's only 8:30 AM in Newfoundland, and even earlier as we go west from there. The time zones East Of Greenwich ("Eogs") are ahead of UTC, so when it's 12:00 UTC it's already 5:30 PM in Dehli, India, and even later as we go east from there. Consider this timeline:

The numbers on the timeline indicate the time zones' offsets from UTC, in hours and minutes. (The math is somewhat counterintuitive, since the SQL Standard requires you to subtract the offset from the local time to calculate UTC.) A time zone's offset from UTC is its <time zone interval>.

As an example, consider an SQL-environment with three installations: one in Vancouver, Canada (with a default time zone offset of +8:00), one in London, England (with a default time zone offset of +0:00) and one in Delhi, India (with a default time zone offset of -5:30). All three installations have access to this Table:

CREATE TABLE Time_Examples (
  Time_Local TIMESTAMP,
  Time_With_Time_Zone TIMESTAMP WITH TIME ZONE);

A user at the London installation adds this row to the Table:

INSERT INTO Time_Examples (Time_Local, Time_With_Time_Zone)
VALUES (TIMESTAMP '1995-07-15 07:30', TIMESTAMP '1995-07-15 07:30');

Now, to a user at the Vancouver installation, this moment in time is equivalent to a local timestamp of '1995-07-14 23:30' (Vancouver time is 8 hours earlier than London time) and to a user at the Delhi installation, the same moment in time is equivalent to a local timestamp of '1995-07-15 13:00' (Delhi time is 5.5 hours after London time). So, despite the fact that "1995-07-15 07:30", "1995-07-14 23:30" and "1995-07-15 13:00" look like three different values, in this case they all, in fact, represent the same absolute moment in time. If each user now does a SELECT on the Table, this is the result they'll see:

Note that the value in the TIME_LOCAL Column stays the same regardless of the installation: a time or timestamp without a <time zone interval> always means "local time" unless the application requires it to take on a time zone offset. The value in the TIME_WITH_TIME_ZONE Column, however, changes with the installation — this is because the <timestamp literal> was forced to take on the default time zone offset at each installation. In this example, the UTC time is equal to the London local time of '1995-07-15 07:30' — i.e.: when the London user selects from the Table, the display shows:

'1995-07-15 07:30+00:00'

to show that the local time is the same as the UTC time; that is, it must be offset by 0 hours and 0 minutes to a UTC time of:

'1995-07-15 07:30'

When the Vancouver user does the same SELECT, however, the display shows:

'1995-07-14 23:30-08:00'

to show that the local time is 8 hours less than the UTC time; that is, it must be offset by 8 hours and 0 minutes to a UTC time of:

'1995-07-15 07:30'

And when the Delhi user does the same SELECT, the display shows:

'1995-07-15 13:00+05:30'

to show that the local time is 5.5 hours greater than the UTC time; that is, it must be offset -5 hours and 30 minutes to a UTC time of:

'1995-07-15 07:30'

Time Zone Offset Arithmetic

Earlier we said that time zone offset arithmetic is somewhat counterintuitive — here's a more detailed explanation. Recall that a time zone offset is the difference between local time and UTC time — say, for example, 4 hours. Then:

• In the case of a time zone that is 4 hours earlier than UTC (e.g.: 12:00 local is 16:00 UTC), the time zone offset is -04:00 (i.e.: local time is 4 hours less than UTC time).

• In the case of a time zone that is 4 hours later than UTC (e.g.: 16:00 UTC is 20:00 local), the time zone offset is +04:00 (i.e.: local time is 4 hours plus UTC time).

The rule is: to get the UTC value, subtract the time zone offset from the time or timestamp. Thus, a local time of '12:00-04:00' evaluates to UTC 16:00 (add the 4 hours, you're subtracting a negative) and a local time of '20:00+04:00' evaluates to UTC 16:00 (subtract the 4 hours).