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 six basic types and operations on them:

DATE                                        a date in the Gregorian calendar YYYY-MM-DD, e.g. 1999-12-31
TIME  [ '(' posint ')' ]                    time of day (HH:MI:SS.sss) with precision (default 0)
TIME  [ '(' posint ')' ] WITH TIME ZONE     time of day (HH:MI:SS.sss) with precision (default 0) and time zone information
TIMESTAMP [ '(' posint ')' ]                date concatenated with unique time, precision
TIMESTAMP [ '(' posint ')' ] WITH TIME ZONE date concatenated with unique time, precision and time zone information
INTERVAL interval_qualifier                 a temporal interval such as: INTERVAL SECOND or INTERVAL MONTH

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

start_field :
		{ YEAR | MONTH | DAY | HOUR | MINUTE }

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 date and time a timezone should be specified in order to translate the local time to GMT (and vice versa if a timestamp is to be decomposed in a local date and time). 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, except 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.

Time. The smallest time is 00:00:00.000 and the largest 23:59:59.999 (the hours in a time range between [0,23], minutes and seconds between [0,59] and milliseconds between [0:999] ). Time 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 seconds" 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 -2147483647 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 [ '(' ')' ]
   | CURRENT_TIMEZONE [ '(' ')' ]
   | LOCALTIME [ '(' ')' ]
   | LOCALTIMESTAMP [ '(' ')' ]
   | NOW [ '(' ')' ]

datetime_field:
     YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | CENTURY | DECADE | QUARTER | WEEK | DOW | DOY

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 longer, 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, skipping 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 1st (in England for instance, it had been starting on March 25th). 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 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 was 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 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 module (as then the duration of a day, which is now the fixed number of 24*60*60*1000 milliseconds, becomes parameterized 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 changes in the timezone rules (e.g., DST only exists since 40 years, and some countries make frequent changes to the DST policy) well. 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.

Extended examples

For the example here we have extended the voyages table in our VOC data set with randomly filled time columns, and also created timestamp columns by appending the random times to the already existing dates. The following columns from the voyages table are used:

The additional columns are created and populated with the following queries:

ALTER TABLE voyages ADD COLUMN departure_time TIME;
ALTER TABLE voyages ADD COLUMN departure_timestamp TIMESTAMP;
ALTER TABLE voyages ADD COLUMN arrival_time TIME;
ALTER TABLE voyages ADD COLUMN arrival_timestamp TIMESTAMP;

UPDATE voyages SET departure_time = STR_TO_TIME(RAND(), '%s');
UPDATE voyages SET arrival_time   = STR_TO_TIME(RAND(), '%s');

UPDATE voyages SET departure_timestamp =  str_to_timestamp(date_to_str(departure_date, '%Y-%m-%d') || ' ' || 
time_to_str(departure_time, '%H:%M'), '%Y-%m-%d %H:%M');
UPDATE voyages SET arrival_timestamp   =  str_to_timestamp(date_to_str(arrival_date, '%Y-%m-%d')   || ' ' || 
time_to_str(arrival_time, '%H:%M'), '%Y-%m-%d %H:%M');

In order to store and work with date and time values, MonetDB defines the following temporal data types:

• DATE: calendar date in the Gregorian calendar, e.g. 1999-12-31.
• TIME: time of day (24-hour clock) with a certain (configurable) precision and time zone.
• TIMESTAMP: date and time data type combined.
• INTERVAL <interval_qualifier>: temporal interval, e.g. 1023.5 seconds, 50 days, 31 months.

Using the current date or time

There are some standard SQL keywords that return the current date, time and timestamp:

These functions are very useful for many use cases. For example, when we need to store the time a tuple was inserted into MonetDB. In that case we can create a table like this:

CREATE TABLE t (a INT, b TIMESTAMP DEFAULT NOW);

If we then insert data into this table as follows (with a short delay between executing the two statements):

INSERT INTO t (a) VALUES (3), (5);
INSERT INTO t (a) VALUES (1);

Then the tuples in table t will have recorded the timestamp they were inserted into MonetDB.

SELECT * FROM t;
+------+----------------------------+
| a    | b                          |
+======+============================+
| 3    | 2015-11-26 09:17:03.328368 |
| 5    | 2015-11-26 09:17:03.328368 |
| 1    | 2015-11-26 09:17:07.795224 |
+------+----------------------------+

A more interesting use case is counting how many voyages started more than 400 years ago:

SELECT COUNT(*) FROM voyages WHERE departure_timestamp < NOW - INTERVAL '400' YEAR;
+------+
| L1   |
+======+
| 319  |
+------+

The above query uses date computation to subtract 400 years from the current date and time. For more information about date computations, see the section below on Date Computation.

Generating dates and times from strings

There are many use cases where we want to have a date or a time that is not at all related to the current date or time. In that case, we can generate date and time types for MonetDB by passing it a string, along with a second string that tells MonetDB how the first string should be interpreted. This is done by using a so called date/time format specifiers.

SELECT str_to_date('23-09-1987', '%d-%m-%Y') AS "date",
       str_to_time('11:40', '%H:%M') AS "time",
       str_to_timestamp('23-09-1987 11:40', '%d-%m-%Y %H:%M') AS "timestamp";
+------------+----------+----------------------------+
| date       | time     | timestamp                  |
+============+==========+============================+
| 1987-09-23 | 11:40:00 | 1987-09-23 11:40:00.000000 |
+------------+----------+----------------------------+

For a complete list of date/time format specifiers, including their meaning, see the section below on date/time format specifiers.

Extracting information from dates and times

Dates and times are stored in such a way that MonetDB can do efficient computations on them. However, sometimes this native format is not what we desire. For example, we might want to create strings from date or time values that is formatted in a way that we can define. This is where the date/time to string conversion functions come in:

SELECT departure_timestamp,
       date_to_str(departure_date, '%Y-%m-%d') AS "date",
       time_to_str(departure_time, '%H:%M') AS "time",
       timestamp_to_str(departure_timestamp, '%Y-%m-%d %H:%M') AS "timestamp"
FROM voyages LIMIT 3;
+----------------------------+------------+-------+------------------+
| departure_timestamp        | date       | time  | timestamp        |
+============================+============+=======+==================+
| 1595-04-02 10:34:46.000000 | 1595-04-02 | 10:34 | 1595-04-02 10:34 |
| 1595-04-02 00:52:57.000000 | 1595-04-02 | 00:52 | 1595-04-02 00:52 |
| 1595-04-02 08:01:55.000000 | 1595-04-02 | 08:01 | 1595-04-02 08:01 |
+----------------------------+------------+-------+------------------+

You can also extract certain portions from date, time and timestamp fields, using the following keywords in combination with the EXTRACT( FROM <date/time/timestamp field>) function:

• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND
• CENTURY
• DECADE
• QUARTER
• WEEK
• DOW
• DOY

We can use this to compute how many years ago the first and the last recorded departure occurred:

SELECT
  MAX(EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM departure_date)) AS first,
  MIN(EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM departure_date)) AS last
FROM voyages;
+-------+------+
| first | last |
+=======+======+
| 420   | 219  |
+-------+------+

So from the year of this writing (2015), the recorded voyage departures were between 219 and 420 years ago.

Now let us use MonetDB to compute something really interesting. The table "total" contains statistics about the number of deaths on every voyage (in the columns "death_at_cape" and "death_during_voyage"). We can join this table with the voyages table and then use the EXTRACT function to group the total number of deaths by the year in which the voyage started. This result can then be used to create a top 10 of the departure years in which the most deaths occurred.

SELECT EXTRACT(YEAR FROM departure_date) AS "year",
       SUM(death_at_cape + death_during_voyage) AS deaths
  FROM voyages JOIN total
    ON voyages.number = total.number
   AND voyages.number_sup = total.number_sup
 GROUP BY "year"
 ORDER BY deaths DESC
 LIMIT 10;
+------+--------+
| year | deaths |
+======+========+
| 1771 | 2993   |
| 1772 | 1094   |
| 1767 | 1016   |
| 1773 | 380    |
| 1766 | 307    |
| 1775 | 298    |
| 1774 | 158    |
| 1726 | 154    |
| 1619 | 151    |
| 1671 | 149    |
+------+--------+

Apparently, between 1766 and 1776, most deaths occurred during voyages.

Date computation

MonetDB can do native computation on two similar date or time fields and on a date or time field and an interval. Subtracting or adding two similar date or time fields is done by doing the computation on its operands converted to days in case of a date field and converted to seconds otherwise. For example:

SELECT CURRENT_TIME - departure_time AS timediff_sec
     , arrival_date - departure_date AS journey_days
FROM voyages LIMIT 3;
+--------------+--------------+
| timediff_sec | journey_days |
+==============+==============+
| 7227.000     | 431          |
| 42136.000    | 431          |
| 16398.000    | 431          |
+--------------+--------------+<

We can use this to get the minimum and the maximum days taken by a voyage.

SELECT MIN(arrival_date - departure_date) AS voyage_min_days,
       MAX(arrival_date - departure_date) AS voyage_max_days
FROM voyages;
+-----------------+-----------------+
| voyage_min_days | voyage_max_days |
+=================+=================+
| -218944         | 73237           |
+-----------------+-----------------+

This result leads us to believe there must be inconsistencies in the data, since negative journey times should not occur and a voyage that took more than 20 years might be unlikely as well.

For other computations, an interval is taken as a second argument and the result is of the same type as the first argument.

We can use this to count the number of voyages that have finished within a year:

SELECT COUNT(*)
FROM voyages
WHERE arrival_date < departure_date + INTERVAL '1' YEAR;
+------+
| L1   |
+======+
| 7339 |
+------+

Time zones

Every MonetDB connection can have an associated time zone:

SELECT NOW;
+----------------------------------+
| current_timestamp                |
+==================================+
| 2015-11-26 13:46:17.643209+01:00 |
+----------------------------------+

This shows the time, assuming we are in time zone GMT +01:00. We can change our current time zone as follows:

SET TIME ZONE INTERVAL '+03:00' HOUR TO MINUTE;

After changing the time zone, the same query now gives a different result:

SELECT NOW;
+----------------------------------+
| current_timestamp                |
+==================================+
| 2015-11-26 15:46:18.378875+03:00 |
+----------------------------------+

As you can see, it adds two hours to the displayed time compared to the previous time we executed this query.

SELECT INTERVAL '2015-09-23' YEAR TO MONTH AS y2m,
       INTERVAL '+01:00' HOUR TO MINUTE AS h2m,
       INTERVAL '+01:30:15' HOUR TO SECOND AS h2s,
       INTERVAL '1 01:30:15' DAY TO SECOND AS d2s;
+-------+----------+----------+-----------+
| y2m   | h2m      | h2s      | d2s       |
+=======+==========+==========+===========+
| 24189 | 3600.000 | 5415.000 | 91815.000 |
+-------+----------+----------+-----------+

Intervals

The arithmetic for date/timestamps is based on intervals.

select current_date +  interval '10'  minute;
+------------+
| %2         |
+============+
| 2021-08-11 |
+------------+
1 tuple

Beware of the down casting towards seconds;

sql>select interval '20' minute  +  interval '10'  minute;
+----------+
| %2       |
+==========+
| 1800.000 |
+----------+
1 tuple
sql>select interval '1' hour  +  interval '10'  minute;
+----------+
| %2       |
+==========+
| 4200.000 |
+----------+
1 tuple

Date/time format specifiers

All possible format specifiers are listed below (taken from Linux Programmer’s Manual)