CREATE EXTENSION pg_nanotime;
SET timezone = 'UTC';
-- storage
SELECT typlen, typbyval, typalign FROM pg_type WHERE typname = 'nanotimestamptz';
 typlen | typbyval | typalign 
--------+----------+----------
     16 | f        | d
(1 row)

-- range boundaries (same as timestamptz)
-- note: PostgreSQL documents "4713 BC" (Julian); in proleptic Gregorian output that is 4714-11-24 BC
SELECT '4714-11-24 00:00:00 UTC BC'::nanotimestamptz AS min_value, '4714-11-24 00:00:00 UTC BC'::timestamptz::text AS pg_min;
              min_value              |             pg_min              
-------------------------------------+---------------------------------
 4714-11-24 00:00:00.000000000+00 BC | Mon Nov 24 00:00:00 4714 UTC BC
(1 row)

SELECT '294276-12-31 23:59:59.999999999 UTC'::nanotimestamptz AS max_value;
             max_value              
------------------------------------
 294276-12-31 23:59:59.999999999+00
(1 row)

SELECT '4714-11-23 23:59:59.999999999 UTC BC'::nanotimestamptz;
ERROR:  timestamp out of range: "4714-11-23 23:59:59 UTC BC"
LINE 1: SELECT '4714-11-23 23:59:59.999999999 UTC BC'::nanotimestamp...
               ^
SELECT '294277-01-01 00:00:00 UTC'::nanotimestamptz;
ERROR:  timestamp out of range: "294277-01-01 00:00:00 UTC"
LINE 1: SELECT '294277-01-01 00:00:00 UTC'::nanotimestamptz;
               ^
-- beyond timestamp9 / int64-ns limits: fine here
SELECT '1677-09-21 00:12:43.145224191 UTC'::nanotimestamptz AS before_int64_min;
         before_int64_min         
----------------------------------
 1677-09-21 00:12:43.145224191+00
(1 row)

SELECT '2262-04-11 23:47:16.854775808 UTC'::nanotimestamptz AS after_int64_max;
         after_int64_max          
----------------------------------
 2262-04-11 23:47:16.854775808+00
(1 row)

SELECT '0001-01-01 00:00:00 UTC'::nanotimestamptz, '9999-12-31 23:59:59.999999999 UTC'::nanotimestamptz;
         nanotimestamptz          |         nanotimestamptz          
----------------------------------+----------------------------------
 0001-01-01 00:00:00.000000000+00 | 9999-12-31 23:59:59.999999999+00
(1 row)

-- pre-1970 and the epoch
SELECT '1969-12-31 23:59:59.999999999 UTC'::nanotimestamptz AS minus_1ns;
            minus_1ns             
----------------------------------
 1969-12-31 23:59:59.999999999+00
(1 row)

SELECT '1970-01-01 00:00:00 UTC'::nanotimestamptz AS epoch;
              epoch               
----------------------------------
 1970-01-01 00:00:00.000000000+00
(1 row)

SELECT '1900-01-01 00:00:00.000000001 UTC'::nanotimestamptz;
         nanotimestamptz          
----------------------------------
 1900-01-01 00:00:00.000000001+00
(1 row)

-- comparison
SELECT '1969-12-31 23:59:59.999999999 UTC'::nanotimestamptz < '1970-01-01 UTC'::nanotimestamptz AS lt,
       '2024-01-01 00:00:00.000000001 UTC'::nanotimestamptz > '2024-01-01 UTC'::nanotimestamptz AS gt,
       '2024-01-01 00:00:00.1 UTC'::nanotimestamptz = '2024-01-01 00:00:00.100000000 UTC'::nanotimestamptz AS eq,
       '2024-01-01 UTC'::nanotimestamptz <> '2024-01-01 00:00:00.000000001 UTC'::nanotimestamptz AS ne;
 lt | gt | eq | ne 
----+----+----+----
 t  | t  | t  | t
(1 row)

SELECT min(t), max(t) FROM (VALUES ('1960-01-01 00:00:00.5 UTC'::nanotimestamptz), ('2024-01-01 UTC'), ('1969-12-31 23:59:59.999999999 UTC')) v(t);
               min                |               max                
----------------------------------+----------------------------------
 1960-01-01 00:00:00.500000000+00 | 2024-01-01 00:00:00.000000000+00
(1 row)

SELECT greatest('1960-01-01 UTC'::nanotimestamptz, '1959-12-31 23:59:59.999999999 UTC'), least('1960-01-01 UTC'::nanotimestamptz, '1959-12-31 23:59:59.999999999 UTC');
             greatest             |              least               
----------------------------------+----------------------------------
 1960-01-01 00:00:00.000000000+00 | 1959-12-31 23:59:59.999999999+00
(1 row)