SET timezone = 'Asia/Taipei'; SELECT typlen, typbyval FROM pg_type WHERE typname = 'nanotimestamp'; typlen | typbyval --------+---------- 16 | f (1 row) -- text I/O ignores any zone, like timestamp SELECT '2024-03-10 02:30:00.123456789+05'::nanotimestamp, '2024-01-01T12:00:00.000000001Z'::nanotimestamp, '1969-12-31 23:59:59.999999999'::nanotimestamp; nanotimestamp | nanotimestamp | nanotimestamp -------------------------------+-------------------------------+------------------------------- 2024-03-10 02:30:00.123456789 | 2024-01-01 12:00:00.000000001 | 1969-12-31 23:59:59.999999999 (1 row) SELECT '4714-11-24 00:00:00 BC'::nanotimestamp AS min, '294276-12-31 23:59:59.999999999'::nanotimestamp AS max; min | max ----------------------------------+--------------------------------- 4714-11-24 00:00:00.000000000 BC | 294276-12-31 23:59:59.999999999 (1 row) SELECT '294277-01-01'::nanotimestamp; ERROR: timestamp out of range: "294277-01-01" LINE 1: SELECT '294277-01-01'::nanotimestamp; ^ SELECT 'infinity'::nanotimestamp; ERROR: infinite timestamps are not supported by nanotimestamp LINE 1: SELECT 'infinity'::nanotimestamp; ^ SELECT '2024-01-01 00:00:00.1234567891'::nanotimestamp; ERROR: more than 9 fractional second digits in "2024-01-01 00:00:00.1234567891" LINE 1: SELECT '2024-01-01 00:00:00.1234567891'::nanotimestamp; ^ SELECT v::text::nanotimestamp = v FROM (VALUES ('2024-01-01 00:00:00.000000001'::nanotimestamp), ('1900-06-15 12:34:56.987654321'), ('0044-03-15 00:00:00 BC')) t(v); ?column? ---------- t t t (3 rows) SET timezone = 'America/New_York'; SELECT '2024-03-10 02:30:00.5'::nanotimestamp; -- nonexistent local time is fine without a zone nanotimestamp ------------------------------- 2024-03-10 02:30:00.500000000 (1 row) SET timezone = 'Asia/Taipei'; -- binary round trip SELECT nanotimestamp_send('1969-12-31 23:59:59.999999999'); nanotimestamp_send ------------------------------------ \xffffffffffffffff000000003b9ac9ff (1 row) -- comparisons, aggregates, B-tree, hash, BRIN SELECT '2024-01-01'::nanotimestamp < '2024-01-01 00:00:00.000000001'::nanotimestamp, '2024-01-01'::nanotimestamp = '2024-01-01'::nanotimestamp; ?column? | ?column? ----------+---------- t | t (1 row) CREATE TEMP TABLE l(t nanotimestamp); INSERT INTO l SELECT '1969-12-31 23:59:00'::nanotimestamp + (g::bigint * 1000000007)::nanointerval FROM generate_series(0, 9999) g; SELECT min(t), max(t), count(DISTINCT t) FROM l; min | max | count -------------------------------+-------------------------------+------- 1969-12-31 23:59:00.000000000 | 1970-01-01 02:45:39.000069993 | 10000 (1 row) CREATE INDEX l_b ON l(t); CREATE INDEX l_h ON l USING hash(t); SET enable_seqscan = off; EXPLAIN (COSTS OFF) SELECT count(*) FROM l WHERE t < '1970-01-01'; QUERY PLAN -------------------------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on l Recheck Cond: (t < '1970-01-01 00:00:00.000000000'::nanotimestamp) -> Bitmap Index Scan on l_b Index Cond: (t < '1970-01-01 00:00:00.000000000'::nanotimestamp) (5 rows) SELECT count(*) FROM l WHERE t < '1970-01-01'; count ------- 60 (1 row) EXPLAIN (COSTS OFF) SELECT count(*) FROM l WHERE t = '1969-12-31 23:59:01.000000007'; QUERY PLAN -------------------------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on l Recheck Cond: (t = '1969-12-31 23:59:01.000000007'::nanotimestamp) -> Bitmap Index Scan on l_h Index Cond: (t = '1969-12-31 23:59:01.000000007'::nanotimestamp) (5 rows) SELECT count(*) FROM l WHERE t = '1969-12-31 23:59:01.000000007'; count ------- 1 (1 row) DROP INDEX l_b; DROP INDEX l_h; CREATE INDEX l_brin ON l USING brin(t); EXPLAIN (COSTS OFF) SELECT count(*) FROM l WHERE t < '1970-01-01'; QUERY PLAN -------------------------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on l Recheck Cond: (t < '1970-01-01 00:00:00.000000000'::nanotimestamp) -> Bitmap Index Scan on l_brin Index Cond: (t < '1970-01-01 00:00:00.000000000'::nanotimestamp) (5 rows) SELECT count(*) FROM l WHERE t < '1970-01-01'; count ------- 60 (1 row) RESET enable_seqscan; -- casts: timestamp (floor), date, nanotimestamptz via session zone, bigint (explicit) SELECT '1969-12-31 23:59:59.999999999'::nanotimestamp::timestamp, timestamp '2024-01-01 00:00:00.5'::nanotimestamp; timestamp | nanotimestamp ---------------------------------+------------------------------- Wed Dec 31 23:59:59.999999 1969 | 2024-01-01 00:00:00.500000000 (1 row) SELECT '1969-12-31 23:59:59.999999999'::nanotimestamp::date, '0001-01-01 BC'::date::nanotimestamp, '2024-02-29'::date::nanotimestamp; date | nanotimestamp | nanotimestamp ------------+----------------------------------+------------------------------- 12-31-1969 | 0001-01-01 00:00:00.000000000 BC | 2024-02-29 00:00:00.000000000 (1 row) SELECT '2024-01-01 12:00:00.123456789'::nanotimestamp::nanotimestamptz, '2024-01-01 12:00:00.123456789+00'::nanotimestamptz::nanotimestamp; nanotimestamptz | nanotimestamp ----------------------------------+------------------------------- 2024-01-01 12:00:00.123456789+08 | 2024-01-01 20:00:00.123456789 (1 row) SELECT ('2024-01-01 12:00:00.123456789'::nanotimestamp::timestamp::timestamptz)::nanotimestamptz = date_trunc('microsecond', '2024-01-01 12:00:00.123456789'::nanotimestamp::nanotimestamptz) AS matches_core; matches_core -------------- t (1 row) SET timezone = 'Europe/Amsterdam'; SELECT '2024-03-31 02:30:00.000000001'::nanotimestamp::nanotimestamptz AS gap, '2024-10-27 02:30:00.000000001'::nanotimestamp::nanotimestamptz AS overlap; gap | overlap ----------------------------------+---------------------------------- 2024-03-31 03:30:00.000000001+02 | 2024-10-27 02:30:00.000000001+01 (1 row) SELECT timestamp '2024-03-31 02:30:00'::timestamptz AS core_gap, timestamp '2024-10-27 02:30:00'::timestamptz AS core_overlap; core_gap | core_overlap -------------------------------+------------------------------ Sun Mar 31 03:30:00 2024 CEST | Sun Oct 27 02:30:00 2024 CET (1 row) SET timezone = 'Asia/Taipei'; SELECT '2024-01-01'::date::nanotimestamptz, '2024-01-01 23:59:59.999999999+08'::nanotimestamptz::date, '2024-01-01 23:59:59.999999999+00'::nanotimestamptz::date; nanotimestamptz | date | date ----------------------------------+------------+------------ 2024-01-01 00:00:00.000000000+08 | 01-01-2024 | 01-02-2024 (1 row) SELECT (-1)::bigint::nanotimestamp, '1970-01-01 00:00:00.000000001'::nanotimestamp::bigint, (-9223372036854775808)::bigint::nanotimestamp; nanotimestamp | int8 | nanotimestamp -------------------------------+------+------------------------------- 1969-12-31 23:59:59.999999999 | 1 | 1677-09-21 00:12:43.145224192 (1 row) SELECT '2262-04-11 23:47:16.854775808'::nanotimestamp::bigint; ERROR: nanotimestamp out of range for bigint SELECT castcontext, castsource::regtype, casttarget::regtype FROM pg_cast WHERE castsource = 'nanotimestamp'::regtype OR casttarget = 'nanotimestamp'::regtype ORDER BY 2::text, 3::text; castcontext | castsource | casttarget -------------+-----------------------------+----------------------------- i | timestamp without time zone | nanotimestamp a | nanotimestamp | timestamp without time zone i | date | nanotimestamp a | nanotimestamp | date i | nanotimestamp | nanotimestamptz a | nanotimestamptz | nanotimestamp e | bigint | nanotimestamp e | nanotimestamp | bigint (8 rows) -- AT TIME ZONE equivalents SELECT timezone('UTC', '2024-01-01 12:00:00.000000001'::nanotimestamp), timezone('America/New_York', '2024-01-01 12:00:00.000000001+00'::nanotimestamptz); timezone | timezone ----------------------------------+------------------------------- 2024-01-01 20:00:00.000000001+08 | 2024-01-01 07:00:00.000000001 (1 row) SELECT timezone('-05:00', '2024-06-01 00:00:00.5'::nanotimestamp)::text = (timestamp '2024-06-01 00:00:00.5' AT TIME ZONE '-05:00')::nanotimestamptz::text AS same_as_core; same_as_core -------------- t (1 row) SELECT '2024-01-01'::nanotimestamp AT TIME ZONE 'UTC'; ERROR: function pg_catalog.timezone(unknown, nanotimestamp) does not exist LINE 1: SELECT '2024-01-01'::nanotimestamp AT TIME ZONE 'UTC'; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- arithmetic SELECT '2024-01-02'::nanotimestamp - '2024-01-01 00:00:00.000000001'::nanotimestamp, '1969-12-31 23:59:59.999999999'::nanotimestamp - '1970-01-01'::nanotimestamp; ?column? | ?column? --------------------+--------------------- 23:59:59.999999999 | -00:00:00.000000001 (1 row) SELECT '2024-01-01'::nanotimestamp + '1 ns'::nanointerval, '1 ns'::nanointerval + '2024-01-01'::nanotimestamp, '2024-01-01'::nanotimestamp - '1 ns'::nanointerval; ?column? | ?column? | ?column? -------------------------------+-------------------------------+------------------------------- 2024-01-01 00:00:00.000000001 | 2024-01-01 00:00:00.000000001 | 2023-12-31 23:59:59.999999999 (1 row) SELECT '2024-01-31 00:00:00.000000005'::nanotimestamp + interval '1 month', interval '1 day' + '2024-02-28 23:00:00.000000001'::nanotimestamp, '2024-03-31'::nanotimestamp - interval '1 month'; ?column? | ?column? | ?column? -------------------------------+-------------------------------+------------------------------- 2024-02-29 00:00:00.000000005 | 2024-02-29 23:00:00.000000001 | 2024-02-29 00:00:00.000000000 (1 row) SET timezone = 'Europe/Amsterdam'; SELECT '2024-03-30 12:00:00.000000001'::nanotimestamp + interval '24 hours' AS no_dst_effect; no_dst_effect ------------------------------- 2024-03-31 12:00:00.000000001 (1 row) SET timezone = 'Asia/Taipei'; SELECT '2024-01-01'::nanotimestamp + '1 day', pg_typeof('2024-01-01'::nanotimestamp + '1 day'), '2024-01-02'::nanotimestamp - '2024-01-01'; ?column? | pg_typeof | ?column? -------------------------------+---------------+---------- 2024-01-02 00:00:00.000000000 | nanotimestamp | 1 day (1 row) SELECT '294276-12-31 23:59:59.999999999'::nanotimestamp + '1 ns'::nanointerval; ERROR: nanotimestamp out of range SELECT '4714-11-24 00:00:00 BC'::nanotimestamp - '1 ns'::nanointerval; ERROR: nanotimestamp out of range SELECT '294276-12-01'::nanotimestamp + interval '1 month'; ERROR: timestamp out of range -- bucket, trunc, extract SELECT time_bucket_ns('1 hour', '1969-12-31 23:30:00.1'::nanotimestamp), time_bucket_ns('15 min', '2024-01-01 10:14:59.999999999'::nanotimestamp), time_bucket_ns('1 day', '2024-01-01 05:00'::nanotimestamp, '2000-01-01 06:00'); time_bucket_ns | time_bucket_ns | time_bucket_ns -------------------------------+-------------------------------+------------------------------- 1969-12-31 23:00:00.000000000 | 2024-01-01 10:00:00.000000000 | 2023-12-31 06:00:00.000000000 (1 row) SELECT time_bucket_ns('7 days', '4714-11-24 00:00:00 BC'::nanotimestamp) AS clamped, pg_typeof(time_bucket_ns('1 s', '2024-01-01'::nanotimestamp)); clamped | pg_typeof ----------------------------------+--------------- 4714-11-24 00:00:00.000000000 BC | nanotimestamp (1 row) SELECT date_trunc('day', '2024-06-15 23:59:59.999999999'::nanotimestamp), date_trunc('microsecond', '2024-06-15 00:00:00.123456789'::nanotimestamp), date_trunc('nanosecond', '2024-06-15 00:00:00.123456789'::nanotimestamp); date_trunc | date_trunc | date_trunc -------------------------------+-------------------------------+------------------------------- 2024-06-15 00:00:00.000000000 | 2024-06-15 00:00:00.123456000 | 2024-06-15 00:00:00.123456789 (1 row) SELECT extract_ns('epoch', '1970-01-01 00:00:01.000000001'::nanotimestamp), extract_ns('epoch', '1969-12-31 23:59:59.999999999'::nanotimestamp), extract_ns('nanosecond', '2024-01-01 00:00:05.000000007'::nanotimestamp), extract_ns('hour', '2024-01-01 05:00'::nanotimestamp); extract_ns | extract_ns | extract_ns | extract_ns -------------+--------------+------------+------------ 1.000000001 | -0.000000001 | 5000000007 | 5 (1 row) SELECT extract_ns('timezone', '2024-01-01'::nanotimestamp); ERROR: unit "timezone" not supported for type timestamp without time zone