Date and time data types

ScopeDB supports data types for managing timestamps.

Data types

ScopeDB supports timestamp and interval for manipulating times.

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 |
+--------+--------+----+-----------+----+--------------+--------------+--------------+

Interval's unit

Interval is always accurate in nanoseconds. Thus, you cannot specify a string containing parts with days or greater (weeks, months, years, etc.), even though ISO 8601 allows them.

Related content

See also date and time functions.