Temporal Types

MonetDB/SQL supports a full set of temporal data types as defined by SQL-92 and ODBC standards:

time_precision :
    '(' posint ')'

posint :
    0
  | 1
  | 2
  | 3
  | 4
  | 5
  | 6

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 year in dates and timestamps that can be stored are between -4712 and 170049.

Dates. Fall in a valid Gregorian calendar 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. Time identifies a valid time-of-day, not an amount of time. It uses 24-hour notation: HH:MI:SS.ssssss The smallest time is 00:00:00.000000 and the largest 23:59:59.999999 (the hours in a time range between [0,23], minutes and seconds between [0,59] and microseconds between [0,999999] ).

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).

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.

Date/Time functions

MonetDB has many built-in functions for processing date, time, timestamp and interval data. See Date Time Functions for more information.

Intervals

Examples

SELECT INTERVAL '2015-09-23' YEAR TO MONTH AS y2m,
       INTERVAL '7' DAY AS d2s,
       INTERVAL '+01:00' HOUR TO MINUTE AS h2m,
       INTERVAL '+01:30:15' HOUR TO SECOND AS h2s,
       INTERVAL '1 01:30:15.789' DAY TO SECOND(3) AS d2ms;
+-------+------------+----------+----------+-----------+
| y2m   | d2s        | h2m      | h2s      | d2ms      |
+=======+============+==========+==========+===========+
| 24189 | 604800.000 | 3600.000 | 5415.000 | 91815.789 |
+-------+------------+----------+----------+-----------+

The arithmetic for dates/timestamps/times is based on intervals (not on numbers).

sql>select date '2021-12-31'  +  interval '10' day;
+------------+
| %2         |
+============+
| 2022-01-10 |
+------------+
sql>select date '2021-12-31'  +  10;
SELECT: no such binary operator 'sys'.'sql_add'(date,tinyint)

Beware of the conversions of interval days/hours/minutes into seconds with milliseconds precision;

sql>select interval '1' day;
+-----------+
| %2        |
+===========+
| 86400.000 |
+-----------+
sql>select interval '20' minute  +  interval '10' minute;
+----------+
| %2       |
+==========+
| 1800.000 |
+----------+
sql>select interval '1' hour  +  interval '1:22' minute to second;
+----------+
| %2       |
+==========+
| 3682.000 |
+----------+
sql>select interval '1:22.345678' minute to second;
+--------+
| %2     |
+========+
| 82.345 |
+--------+

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');

Using the current date or time

There are some standard SQL keywords that return the current date, time and timestamp. They are also called psuedo columns:

SELECT CURRENT_DATE as "CURRENT_DATE", CURRENT_TIME as "CURRENT_TIME", CURRENT_TIMESTAMP as "CURRENT_TIMESTAMP";
+--------------+-----------------------+----------------------------------+
| CURRENT_DATE | CURRENT_TIME          | CURRENT_TIMESTAMP                |
+==============+=======================+==================================+
| 2015-11-26   | 18:01:56.109614+02:00 | 2015-11-26 18:01:56.109615+02:00 |
+--------------+-----------------------+----------------------------------+

SELECT NOW as "NOW", LOCALTIME as "LOCALTIME", LOCALTIMESTAMP as "LOCALTIMESTAMP";
+----------------------------------+-----------------+----------------------------+
| NOW                              | LOCALTIME       | LOCALTIMESTAMP             |
+==================================+=================+============================+
| 2015-11-26 18:02:45.413240+02:00 | 18:02:45.413377 | 2015-11-26 18:02:45.413378 |
+----------------------------------+-----------------+----------------------------+

These psuedo columns are very useful for many use cases. For example, when we need to register 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(*) AS count FROM voyages WHERE departure_timestamp < NOW - INTERVAL '400' YEAR;
+------+
| count|
+======+
| 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 construct date, time and timestamp values for MonetDB. This can be done using cast operator or cast or convert functions or when the format is not ISO standard via functions: str_to_date(val, format), str_to_time(val, format) or str_to_timestamp(val, format). The first argument is the value as string, the second argument is the format string how it should be interpreted. For a complete list of date/time format specifiers, including their meaning, see date/time format specifiers.

-- using cast operators
SELECT date '1987-09-23' AS "date",
       time '11:40' AS "time",
       timestamp '1987-09-23 11:40' AS "timestamp";
+------------+----------+----------------------------+
| date       | time     | timestamp                  |
+============+==========+============================+
| 1987-09-23 | 11:40:00 | 1987-09-23 11:40:00.000000 |
+------------+----------+----------------------------+

-- using cast() functions
SELECT cast('1987-09-23' as date) AS "date",
       cast('11:40' as time) AS "time",
       cast('1987-09-23 11:40' as timestamp) AS "timestamp";
+------------+----------+----------------------------+
| date       | time     | timestamp                  |
+============+==========+============================+
| 1987-09-23 | 11:40:00 | 1987-09-23 11:40:00.000000 |
+------------+----------+----------------------------+

-- using convert() functions
SELECT convert('1987-09-23', date) AS "date",
       convert('11:40', time) AS "time",
       convert('1987-09-23 11:40', timestamp) AS "timestamp";
+------------+----------+----------------------------+
| date       | time     | timestamp                  |
+============+==========+============================+
| 1987-09-23 | 11:40:00 | 1987-09-23 11:40:00.000000 |
+------------+----------+----------------------------+

-- using str_to_...() functions
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 | 13:40:00+02:00 | 1987-09-23 13:40:00.000000+02:00 |
+------------+----------------+----------------------------------+

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/timestamp 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 parts from date, time and timestamp fields, using the following keywords in combination with the EXTRACT( FROM <date/time/timestamp field/expression>) function:

datetime_func:
     EXTRACT '(' datetime_field FROM scalar_exp ')'

datetime_field:
     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(*) AS count
FROM voyages
WHERE arrival_date < departure_date + INTERVAL '1' YEAR;
+------+
| count|
+======+
| 7339 |
+------+

Time zones

Every MonetDB connection can have an associated time zone:

SELECT NOW as current_timestamp;
+----------------------------------+
| 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 as current_timestamp;
+----------------------------------+
| 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.