Date and Time functionality

Date and Time functionality zhang Tue, 09/05/2017 - 12:38

MonetDB offers many functionalities for storing and working with date and time values.

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:

Column name Data type In original data set Description
departure_date date yes The data at which the voyage started
departure_time time no The time at which the voyage started
departure_timestamp timestamp no Combination of departure_date and departure_time
arrival_date date yes The date at which the voyage ended
arrival_time time no The time at which the voyage ended
arrival_timestamp timestamp no Combination of arrival_date and arrival_time

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

Temporal types

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-Jan-31.
  • TIME: time of day with a certain (configurable) precision and time zone.
  • TIMESTAMP: date and time data type combined.
  • INTERVAL: temporal interval, e.g. 1023.5 seconds or 31 months.

Using the current date or time

There are some SQL constants that return date and time values derived from the current date and time:

Query Example result With time zone Result type
SELECT CURRENT_DATE; 2015-05-22 no date
SELECT CURRENT_TIME; 14:18:17.000000+02:00 yes time
SELECT CURRENT_TIMESTAMP; 2015-05-22 14:18:17.000000+02:00 yes timestamp
SELECT NOW; 2015-05-22 14:18:17.000000+02:00 yes timestamp
SELECT LOCALTIME; 14:18:17.000000 no time
SELECT LOCALTIMESTAMP; 2015-05-22 14:18:17.000000 no 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 t will have recorded the time they were inserted into MonetDB:

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

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

  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • SECOND

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 ~1760 and 1780, it was not the safest time to travel.

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.000000+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:17.000000+03:00 |
+----------------------------------+

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

Interval conversions

As you might have noticed in the previous section, there is an interval conversion function that takes an 'hour' string and converts it to minutes. Strictly speaking, this is an interval with arguments, and the following interval/argument combinations are possible:

  • YEAR TO MONTH
  • DAY TO SECOND
  • HOUR TO MINUTE
  • HOUR TO SECOND

Example usage:

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;
+-------+----------+----------+
| y2m   | h2m      | h2s      |
+=======+==========+==========+
| 24189 | 3600.000 | 5415.000 |
+-------+----------+----------+

Date/time format specifiers

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

 %a   The abbreviated name of the day of the week according to the current locale.
 %A   The full name of the day of the week according to the current locale.
 %b   The abbreviated month name according to the current locale.
 %B   The full month name according to the current locale.
 %c   The preferred date and time representation for the current locale.
 %C   The century number (year/100) as a 2-digit integer. (SU)
 %d   The day of the month as a decimal number (range 01 to 31).
 %D   Equivalent to %m/%d/%y. (Yecch—for Americans only. Americans should note that in other countries %d/%m/%y is rather common. This means that in international context this format is ambiguous and should not be used.) (SU)
 %e   Like %d, the day of the month as a decimal number, but a leading zero is replaced by a space. (SU)
 %E   Modifier: use alternative format, see below. (SU)
 %F   Equivalent to %Y-%m-%d (the ISO 8601 date format). (C99)
 %G   The ISO 8601 week-based year (see NOTES) with century as a decimal number. The 4-digit year corresponding to the ISO week number (see %V). This has the same format and value as %Y, except that if the ISO week number belongs to the previous or next year, that year is used instead. (TZ)
 %g   Like %G, but without century, that is, with a 2-digit year (00-99). (TZ)
 %h   Equivalent to %b. (SU)
 %H   The hour as a decimal number using a 24-hour clock (range 00 to 23).
 %I   The hour as a decimal number using a 12-hour clock (range 01 to 12).
 %j   The day of the year as a decimal number (range 001 to 366).
 %k   The hour (24-hour clock) as a decimal number (range 0 to 23); single digits are preceded by a blank. (See also %H.) (TZ)
 %l   The hour (12-hour clock) as a decimal number (range 1 to 12); single digits are preceded by a blank. (See also %I.) (TZ)
 %m   The month as a decimal number (range 01 to 12).
 %M   The minute as a decimal number (range 00 to 59).
 %n   A newline character. (SU)
 %O   Modifier: use alternative format, see below. (SU)
 %p   Either "AM" or "PM" according to the given time value, or the corresponding strings for the current locale. Noon is treated as "PM" and midnight as "AM".
 %P   Like %p but in lowercase: "am" or "pm" or a corresponding string for the current locale. (GNU)
 %r   The time in a.m. or p.m. notation. In the POSIX locale this is equivalent to %I:%M:%S %p. (SU)
 %R   The time in 24-hour notation (%H:%M). (SU) For a version including the seconds, see %T below.
 %s   The number of seconds since the Epoch, 1970-01-01 00:00:00 +0000 (UTC). (TZ)
 %S   The second as a decimal number (range 00 to 60). (The range is up to 60 to allow for occasional leap seconds.)
 %t   A tab character. (SU)
 %T   The time in 24-hour notation (%H:%M:%S). (SU)
 %u   The day of the week as a decimal, range 1 to 7, Monday being 1. See also %w. (SU)
 %U   The week number of the current year as a decimal number, range 00 to 53, starting with the first Sunday as the first day of week 01. See also %V and %W.
 %V   The ISO 8601 week number (see NOTES) of the current year as a decimal number, range 01 to 53, where week 1 is the first week that has at least 4 days in the new year. See also %U and %W. (SU)
 %w   The day of the week as a decimal, range 0 to 6, Sunday being 0. See also %u.
 %W   The week number of the current year as a decimal number, range 00 to 53, starting with the first Monday as the first day of week 01.
 %x   The preferred date representation for the current locale without the time.
 %X   The preferred time representation for the current locale without the date.
 %y   The year as a decimal number without a century (range 00 to 99).
 %Y   The year as a decimal number including the century.
 %z   The +hhmm or -hhmm numeric timezone (that is, the hour and minute offset from UTC). (SU)
 %Z   The timezone name or abbreviation.
 %+   The date and time in date(1) format. (TZ) (Not supported in glibc2.)
 %%   A literal '%' character.