Date and time data types

ScopeDB supports timestamp and interval for manipulating date and time.

Timestamp

Timestamp internally stores UNIX epoch in nanoseconds in 64-bit integer. Thus, the minimum and maximum value of timestamp are:

SELECT
    (-9223372036854775808)::timestamp AS min_ts,
    (9223372036854775807)::timestamp AS max_ts;

+--------------------------------+--------------------------------+
| min_ts                         | max_ts                         |
+--------------------------------+--------------------------------+
| 1677-09-21T00:12:43.145224192Z | 2262-04-11T23:47:16.854775807Z |
+--------------------------------+--------------------------------+

You can also create timestamps with string literal that follows RFC 3339 or RFC 9557:

SELECT
    '1985-04-12T23:20:50.52Z'::timestamp AS t1_3339,
    '1937-01-01T12:00:27.87+00:20'::timestamp AS t2_3339,
    '2022-07-08T00:14:07-08:45[-08:45]'::timestamp AS t1_9557,
    '2022-07-08T00:14:07+01:00[Europe/Paris]'::timestamp AS t2_9557;

+-------------------------+-------------------------+----------------------+----------------------+
| t1_3339                 | t2_3339                 | t1_9557              | t2_9557              |
+-------------------------+-------------------------+----------------------+----------------------+
| 1985-04-12T23:20:50.52Z | 1937-01-01T11:40:27.87Z | 2022-07-08T08:59:07Z | 2022-07-07T23:14:07Z |
+-------------------------+-------------------------+----------------------+----------------------+

Interval

Interval internally stores nanoseconds in 64-bit integer. Thus, the minimum and maximum value of interval are:

SELECT
    (-9223372036854775808)::interval AS min_interval,
    (9223372036854775807)::interval AS max_interval;

+----------------------------------------+------------------------------------+
| min_interval                           | max_interval                       |
+----------------------------------------+------------------------------------+
| 2562047h 47m 16s 854ms 775µs 808ns ago | 2562047h 47m 16s 854ms 775µs 807ns |
+----------------------------------------+------------------------------------+

You can also create interval with string literal that follows the "Durations" syntax in ISO 8601:

SELECT
    'PT1s'::interval AS i1,
    'PT59.7788s'::interval AS i2,
    'PT25h'::interval AS i3,
    'PT25h60s'::interval AS i4,
    '-PT26h59.7788s'::interval AS i5;

+----+-----------------+-----+--------+-------------------------+
| i1 | i2              | i3  | i4     | i5                      |
+----+-----------------+-----+--------+-------------------------+
| 1s | 59s 778ms 800µs | 25h | 25h 1m | 26h 59s 778ms 800µs ago |
+----+-----------------+-----+--------+-------------------------+

Alternatively, you can create interval with string literal in the "friendly" duration format:

SELECT
  '2h30m'::interval AS i1,
  '2h 30m'::interval AS i2,
  '1m'::interval AS i3,
  '0.0021s'::interval AS i4,
  '0s'::interval AS i5,
  '3 mins 34s 123ms'::interval AS i6,
  '3 mins 34.123 secs'::interval AS i7,
  '3 mins 34,123s'::interval AS i8;

+--------+--------+----+-----------+----+--------------+--------------+--------------+
| i1     | i2     | i3 | i4        | i5 | i6           | i7           | i8           |
+--------+--------+----+-----------+----+--------------+--------------+--------------+
| 2h 30m | 2h 30m | 1m | 2ms 100µs | 0s | 3m 34s 123ms | 3m 34s 123ms | 3m 34s 123ms |
+--------+--------+----+-----------+----+--------------+--------------+--------------+