Skip to main content

Temporal types

MonetDB/SQL supports a rich set of time-related operations within the kernel. The starting point are SQL 92 and the ODBC time-related data types.  There are four basic types and operations on them:

DATE a date in the Gregorian calendar, e.g. 1999-JAN-31
DAYTIME a time of day to the detail of milliseconds, e.g. 23:59:59:000
TIME  [ '(' posint ')' ] [WITH TIME ZONE] time of day with precision and time zone
TIMESTAMP [ '(' posint ')' ] [WITH TIME ZONE] date concatenated with unique time, precision and time zone

INTERVAL interval_qualifier

a temporal interval

interval_qualifier :
     { YEAR | MONTH  | DAY  | HOUR | MINUTE | SECOND  } [time_precision]
   |  start_field  TO  end_field

start_field :
   
{ YEAR | MONTH  | DAY | HOUR | MINUTE  } [time_precision]

end_field:
     
{ YEAR | MONTH  | DAY | HOUR | MINUTE | SECOND  [time_precision] }

time_precision :
     '(' posint ')'

A timestamp is a combination of date and time, indicating an exact point in time (GMT). GMT is the time at the Greenwich meridian without a daylight savings time (DST) regime. Absence of DST means that hours are consecutive (no jumps) which makes it easy to perform time difference calculations.

The local time is often different from GMT (even at Greenwich in summer, as the UK also has DST). Therefore, whenever a timestamp is composed from a local daytime and date, a timezone should be specified in order to translate the local daytime to GMT (and vice versa if a timestamp is to be decomposed in a local date and daytime). To adjust the local time can issue a command such as SET TIME ZONE INTERVAL '1' HOUR TO MINUTE.

We provide predefined timezone objects for a number of timezones (see below). Also, there is one timezone called the local timezone, which can be set to one global value in a running MonetDB server, that is used if the timezone parameter is omitted from a command that needs it (if not set, the default value of the local timezone is plain GMT).

The value ranges and lexical denotations are defined as follows:

Min and max years. The maximum and minimum dates and timestamps that can be stored are in the years 5,867,411 and -5,867,411, respectively. Interestingly, the year 0 is not a valid year. The year before 1 is called -1.

Dates. Fall in a valid year, and have a month and day that is valid in that year. The first day in the year is January 1, the last December 31. Months with 31 days are January, March, May, July, August, October, and December, while April, June, September and November have 30 days. February has 28 days, expect in a leap year, when it has 29. A leap year is a year that is an exact multiple of 4. Years that are a multiple of 100 but not of 400 are an exception; they are no leap years.

Daytime. The smallest daytime is 00:00:00:000 and the largest 23:59:59:999 (the hours in a daytime range between [0,23, minutes and seconds between [0,59 and milliseconds between [0:999). Daytime identifies a valid time-of-day, not an amount of time (for denoting amounts of time, or time differences, we use here concepts like "number of days" or "number of milliseconds" denoted by some value of a standard integer type).

Timestamp. A valid timestamp is formed by a combination of a valid date and valid daytime. When creating a timestamp from a date and daytime, a timezone should be specified (if timezone is omitted, the local timezone is assumed). If a timezone is specified, it is used to convert the date and time in that timezone to GMT, which is the internal timestamp representation. One problem here is that the first hour after DST has ended (some Sunday night in autumn, generally), the time is set back one hour, so the same times occur twice. Hence two translations to a timestamp are possible for such date and time combinations. In those case, we act as if it was the first occurrence (still within DST).

For difference calculations between dates (in numbers of days) we use signed integer. Hence, the valid range for difference calculations is between -2147483648 and 2147483647 days (which corresponds to roughly -5,867,411 and 5,867,411 years).

For difference between timestamps (in numbers of milliseconds) we use 64-bit bigint. These are large integers of maximally 19 digits, which therefore impose a limit of about 106,000,000,000 years on the maximum time difference used in computations.
 

datetime_func:
     EXTRACT '(' datetime_field FROM scalar_exp ')'
   | CURRENT_DATE [ '(' ')' ]
   | CURRENT_TIME [ '(' ')' ]
   | CURRENT_TIMESTAMP [ '(' ')' ]
   | LOCALTIME [ '(' ')' ]
   | LOCALTIMESTAMP [ '(' ')' ]
   | CURRENT_TIME [ '(' ')']

datetime_field: { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND

Gregorian dates.
The basics of the Gregorian calendar stem from the time of Julius Caesar, when the concept of a solar year as consisting of 365.25 days (365 days plus once in 4 years one extra day) was introduced. However, this Julian Calendar, made a year 11 minutes long, which subsequently accumulated over the ages, causing a shift in seasons. In medieval times this was noticed, and in 1582 Pope Gregory XIII issued a decree, skipped 11 days. This measure was not adopted in the whole of Europe immediately, however.  For this reason, there were many regions in Europe that upheld different dates.

It was only on September 14, 1752 that some consensus was reached and more countries joined the Gregorian Calendar, which also was last modified at that time. The modifications
were twofold: first, 12 more days were skipped. Second, it was determined that the year starts on January 1 (in England, for instance, it had been starting on March 25). Other parts of the world have adopted the Gregorian Calendar even later.

MonetDB implements the Gregorian Calendar in all its regularity. This means that values before the year 1752 probably do not correspond with the dates that people really used in times before that (what they did use, however, was very vague anyway, as explained above). In solar terms, however, this calendar is reasonably accurate (see the "correction seconds" note below).

Timezones
The basic timezone regime was established on November 1, 1884 in the International Meridian Conference held in Greenwich (UK). Before that, a different time held in almost any city. The conference established 24 different time zones defined by regular longitude intervals that all differed by one hour.  Not for long it was that national and political interest started to erode this nicely regular system.  Timezones now often follow country borders, and some regions (like the Guinea areas in Latin America) have times that differ with a 15 minute grain from GMT rather than an hour or even half-an-hour grain.

An extra complication became the introduction of daylight saving time (DST), which causes a time jump in spring, when the clock is skips one hour and in autumn, when the
clock is set back one hour (so in a one hour span, the same times occur twice). The DST regime is a purely political decision made on a country-by-country basis. Countries in the same timezone can have different DST regimes. Even worse, some countries have DST in some years, and not in other years.

To avoid confusion, this temporal type module stores absolute points of time in GMT only (GMT does not have a DST regime). When storing local times in the database, or retrieving local times from absolute timestamps, a correct timezone object should be used for the conversion.

Applications that do not make correct use of timezones, will produce irregular results on e.g. time difference calculations.

Correction seconds
Once every such hundred years, a correction second is added on new year's night. This rule would seriously complicate the temporal type mofule (as then the duration of a day, which is now the fixed number of 24*60*60*1000 milliseconds, becomes parametrized by the date), it is not implemented. Hence these seconds are lost, so time difference calculations in milliseconds (rather than in days) have a small error if the time difference spans many hundreds of years.

We cannot handle well changes in the timezone rules (e.g., DST only exists since 40 years, and some countries make frequent changes to the DST policy). To accommodate this we should make timezone_local a function with a year parameter. The tool should maintain and access a timezone database. Lookup of the correct timezone would be dynamic in this structure. The timezone_setlocal would just set the string name of the timezone.

Timezones

Wake Island GMT+12:00
Melbourne/Australia GMT+11:00
Brisbane/Australia GMT+10:00
Japan GMT+09:00
Singapore GMT+08:00
Thailand GMT+07:00
Pakistan GMT+05:00
United Arab Emirates GMT+04:00&GMT
GMT  
Azore Islands GMT-01:00
Hawaii/USA GMT-10:00
American Samoa GMT-11:00
Kazakhstan GMT+06:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
Moscow/Russia GMT+03:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
East/Europe GMT+02:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
West/Europe GMT+01:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
UK GMT-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
Eastern/Brazil GMT-02:00-DST[first sunday from end of october@02:01,first sunday from end of march@02:01
Western/Brazil GMT-03:00-DST[first sunday from end of october@02:01,first sunday from end of march@02:01
Andes/Brazil GMT-04:00-DST[first sunday from end of october@02:01,first sunday from end of march@02:01
East/USA GMT-05:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
Central/USA GMT-06:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
Mountain/USA GMT-07:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01
Alaska/USA GMT-09:00-DST[first sunday from end of march@02:01,first sunday from end of october@02:01