CREATE EXTENSION pg_duration; -- Valid duration input CREATE TEMP TABLE input_table (s text); INSERT INTO input_table VALUES ('42 hour'), ('43 hours'), ('44 h'), ('45 minute'), ('46 minutes'), ('47 m'), ('48 second'), ('49 seconds'), ('50 s'), ('51 millisecond'), ('52 milliseconds'), ('53 ms'), ('54 microsecond'), ('55 microseconds'), ('56 us'), ('1 minute 2 h 3 microseconds 4 second 5 ms'); SELECT s, s::duration AS dur, ('-' || s)::duration AS neg_dur FROM input_table; s | dur | neg_dur -------------------------------------------+-------------------------------+-------------------------------- 42 hour | @ 42 hours | @ 42 hours ago 43 hours | @ 43 hours | @ 43 hours ago 44 h | @ 44 hours | @ 44 hours ago 45 minute | @ 45 mins | @ 45 mins ago 46 minutes | @ 46 mins | @ 46 mins ago 47 m | @ 47 mins | @ 47 mins ago 48 second | @ 48 secs | @ 48 secs ago 49 seconds | @ 49 secs | @ 49 secs ago 50 s | @ 50 secs | @ 50 secs ago 51 millisecond | @ 0.051 secs | @ 0.051 secs ago 52 milliseconds | @ 0.052 secs | @ 0.052 secs ago 53 ms | @ 0.053 secs | @ 0.053 secs ago 54 microsecond | @ 0.000054 secs | @ 0.000054 secs ago 55 microseconds | @ 0.000055 secs | @ 0.000055 secs ago 56 us | @ 0.000056 secs | @ 0.000056 secs ago 1 minute 2 h 3 microseconds 4 second 5 ms | @ 2 hours 1 min 4.005003 secs | @ 1 hour 59 mins 4.005003 secs (16 rows) SELECT duration '36 minutes ago'; duration --------------- @ 36 mins ago (1 row) SELECT duration '20:13:41'; duration ---------------------------- @ 20 hours 13 mins 41 secs (1 row) SELECT duration 'PT20:13:41'; duration ---------------------------- @ 20 hours 13 mins 41 secs (1 row) -- Invalid duration input SELECT duration '100 millennium'; ERROR: invalid units for duration LINE 1: SELECT duration '100 millennium'; ^ SELECT duration '101 millenniums'; ERROR: invalid units for duration LINE 1: SELECT duration '101 millenniums'; ^ SELECT duration '102 century'; ERROR: invalid units for duration LINE 1: SELECT duration '102 century'; ^ SELECT duration '103 centuries'; ERROR: invalid units for duration LINE 1: SELECT duration '103 centuries'; ^ SELECT duration '104 decade'; ERROR: invalid units for duration LINE 1: SELECT duration '104 decade'; ^ SELECT duration '105 decades'; ERROR: invalid units for duration LINE 1: SELECT duration '105 decades'; ^ SELECT duration '106 year'; ERROR: invalid units for duration LINE 1: SELECT duration '106 year'; ^ SELECT duration '107 years'; ERROR: invalid units for duration LINE 1: SELECT duration '107 years'; ^ SELECT duration '108 y'; ERROR: invalid units for duration LINE 1: SELECT duration '108 y'; ^ SELECT duration '109 month'; ERROR: invalid units for duration LINE 1: SELECT duration '109 month'; ^ SELECT duration '110 months'; ERROR: invalid units for duration LINE 1: SELECT duration '110 months'; ^ SELECT duration '111 week'; ERROR: invalid units for duration LINE 1: SELECT duration '111 week'; ^ SELECT duration '112 weeks'; ERROR: invalid units for duration LINE 1: SELECT duration '112 weeks'; ^ SELECT duration '113 w'; ERROR: invalid units for duration LINE 1: SELECT duration '113 w'; ^ SELECT duration '114 day'; ERROR: invalid units for duration LINE 1: SELECT duration '114 day'; ^ SELECT duration '115 days'; ERROR: invalid units for duration LINE 1: SELECT duration '115 days'; ^ SELECT duration '116 d'; ERROR: invalid units for duration LINE 1: SELECT duration '116 d'; ^ SELECT duration '5 seconds 2 days'; ERROR: invalid units for duration LINE 1: SELECT duration '5 seconds 2 days'; ^ SELECT duration 'P1995-08-06T20:13:41'; ERROR: invalid units for duration LINE 1: SELECT duration 'P1995-08-06T20:13:41'; ^ -- Comparison CREATE TEMP TABLE cmp_table(a duration, b duration); INSERT INTO cmp_table VALUES ('1 sec', '1 hour'), ('1 millisecon', '1 ms'), ('1 minute', '1 us'); SELECT a, b, a < b AS lt, a <= b AS le, a > b AS gt, a >= b AS ge, a = b AS eq, a != b AS ne FROM cmp_table; a | b | lt | le | gt | ge | eq | ne --------------+-----------------+----+----+----+----+----+---- @ 1 sec | @ 1 hour | t | t | f | f | f | t @ 0.001 secs | @ 0.001 secs | f | t | f | t | t | f @ 1 min | @ 0.000001 secs | f | f | t | t | f | t (3 rows) -- Arithmetic -- Valid SELECT - duration '10 minutes'; ?column? --------------- @ 10 mins ago (1 row) SELECT duration '55 seconds' + duration '12 hours'; ?column? -------------------- @ 12 hours 55 secs (1 row) SELECT duration '2 hours' - duration '30 minutes'; ?column? ------------------ @ 1 hour 30 mins (1 row) SELECT duration '42 minutes' * 10.5; ?column? ------------------- @ 7 hours 21 mins (1 row) SELECT duration '42 minutes' * 0.0; ?column? ---------- @ 0 (1 row) SELECT duration '5 hours 40 minutes 30 s' / 3.7; ?column? -------------------------------- @ 1 hour 32 mins 1.621622 secs (1 row) -- Invalid SELECT - duration '-9223372036854775807 us'; ERROR: duration out of range SELECT duration '9223372036854775806 us' + duration '1 us'; ERROR: duration out of range SELECT duration '9223372036854775806 us' + duration '2 us'; ERROR: duration out of range SELECT duration '-9223372036854775807 us' - duration '1 us'; ERROR: duration out of range SELECT duration '-9223372036854775807 us' - duration '2 us'; ERROR: duration out of range SELECT duration '4611686018427387903 us' * 2.5; ERROR: duration out of range SELECT duration '4611686018427387903 us' * 'nan'::float8; ERROR: duration out of range SELECT duration '4611686018427387903 us' * 'infinity'::float8; ?column? ---------- infinity (1 row) SELECT duration '4611686018427387903 us' * '-infinity'::float8; ?column? ----------- -infinity (1 row) SELECT duration '0 us' * 'infinity'::float8; ERROR: duration out of range SELECT duration '0 us' * '-infinity'::float8; ERROR: duration out of range SELECT duration '-4611686018427387904 us' / 0.5; ERROR: duration out of range SELECT duration '4611686018427387904 us' / 0.0; ERROR: division by zero SELECT duration '4611686018427387904 us' / 'nan'::float8; ERROR: duration out of range SELECT duration '4611686018427387903 us' / 'infinity'::float8; ?column? ---------- @ 0 (1 row) SELECT duration '4611686018427387903 us' / '-infinity'::float8; ?column? ---------- @ 0 (1 row) -- Functions helper table CREATE TEMP TABLE valid_fields(f text); INSERT INTO valid_fields VALUES ('hour'), ('minute'), ('second'), ('millisecond'), ('microsecond'); CREATE TEMPORARY TABLE func_table (d duration); INSERT INTO func_table VALUES (duration '1 hour 2 minutes 3 seconds 4 milliseconds 5 microseconds'), (-duration '1 hour 2 minutes 3 seconds 4 milliseconds 5 microseconds'); -- make_duration SELECT make_duration(); make_duration --------------- @ 0 (1 row) SELECT make_duration(1); make_duration --------------- @ 1 hour (1 row) SELECT make_duration(1, 2); make_duration ----------------- @ 1 hour 2 mins (1 row) SELECT make_duration(1, 2, 3.456); make_duration ---------------------------- @ 1 hour 2 mins 3.456 secs (1 row) SELECT make_duration(2147483647, 2147483647, 9223372036854775807); ERROR: duration out of range -- isfinite SELECT isfinite(duration '112 hours'); isfinite ---------- t (1 row) SELECT isfinite(duration 'infinity'); isfinite ---------- f (1 row) SELECT isfinite(duration '-infinity'); isfinite ---------- f (1 row) -- date_trunc SELECT f, d, date_trunc(f, d) FROM valid_fields, func_table; f | d | date_trunc -------------+-----------------------------------+----------------------------------- hour | @ 1 hour 2 mins 3.004005 secs | @ 1 hour minute | @ 1 hour 2 mins 3.004005 secs | @ 1 hour 2 mins second | @ 1 hour 2 mins 3.004005 secs | @ 1 hour 2 mins 3 secs millisecond | @ 1 hour 2 mins 3.004005 secs | @ 1 hour 2 mins 3.004 secs microsecond | @ 1 hour 2 mins 3.004005 secs | @ 1 hour 2 mins 3.004005 secs hour | @ 1 hour 2 mins 3.004005 secs ago | @ 1 hour ago minute | @ 1 hour 2 mins 3.004005 secs ago | @ 1 hour 2 mins ago second | @ 1 hour 2 mins 3.004005 secs ago | @ 1 hour 2 mins 3 secs ago millisecond | @ 1 hour 2 mins 3.004005 secs ago | @ 1 hour 2 mins 3.004 secs ago microsecond | @ 1 hour 2 mins 3.004005 secs ago | @ 1 hour 2 mins 3.004005 secs ago (10 rows) SELECT date_trunc('epoch', d) FROM func_table; ERROR: unit "epoch" not recognized for type duration SELECT date_trunc('millennium', d) FROM func_table; ERROR: unit "millennium" not supported for type duration SELECT date_trunc('century', d) FROM func_table; ERROR: unit "century" not supported for type duration SELECT date_trunc('decade', d) FROM func_table; ERROR: unit "decade" not supported for type duration SELECT date_trunc('year', d) FROM func_table; ERROR: unit "year" not supported for type duration SELECT date_trunc('quarter', d) FROM func_table; ERROR: unit "quarter" not supported for type duration SELECT date_trunc('month', d) FROM func_table; ERROR: unit "month" not supported for type duration SELECT date_trunc('week', d) FROM func_table; ERROR: unit "week" not supported for type duration SELECT date_trunc('day', d) FROM func_table; ERROR: unit "day" not supported for type duration -- date_part SELECT f, d, date_part(f, d) FROM valid_fields, func_table; f | d | date_part -------------+-----------------------------------+----------- hour | @ 1 hour 2 mins 3.004005 secs | 1 minute | @ 1 hour 2 mins 3.004005 secs | 2 second | @ 1 hour 2 mins 3.004005 secs | 3.004005 millisecond | @ 1 hour 2 mins 3.004005 secs | 3004.005 microsecond | @ 1 hour 2 mins 3.004005 secs | 3004005 hour | @ 1 hour 2 mins 3.004005 secs ago | -1 minute | @ 1 hour 2 mins 3.004005 secs ago | -2 second | @ 1 hour 2 mins 3.004005 secs ago | -3.004005 millisecond | @ 1 hour 2 mins 3.004005 secs ago | -3004.005 microsecond | @ 1 hour 2 mins 3.004005 secs ago | -3004005 (10 rows) SELECT date_part('epoch', d) FROM func_table; date_part -------------- 3723.004005 -3723.004005 (2 rows) SELECT date_part('millennium', d) FROM func_table; ERROR: unit "millennium" not supported for type duration SELECT date_part('century', d) FROM func_table; ERROR: unit "century" not supported for type duration SELECT date_part('decade', d) FROM func_table; ERROR: unit "decade" not supported for type duration SELECT date_part('year', d) FROM func_table; ERROR: unit "year" not supported for type duration SELECT date_part('quarter', d) FROM func_table; ERROR: unit "quarter" not supported for type duration SELECT date_part('month', d) FROM func_table; ERROR: unit "month" not supported for type duration SELECT date_part('week', d) FROM func_table; ERROR: unit "week" not supported for type duration SELECT date_part('day', d) FROM func_table; ERROR: unit "day" not supported for type duration -- extract_duration SELECT f, d, extract_duration(f, d) FROM valid_fields, func_table; f | d | extract_duration -------------+-----------------------------------+------------------ hour | @ 1 hour 2 mins 3.004005 secs | 1 minute | @ 1 hour 2 mins 3.004005 secs | 2 second | @ 1 hour 2 mins 3.004005 secs | 3.004005 millisecond | @ 1 hour 2 mins 3.004005 secs | 3004.005 microsecond | @ 1 hour 2 mins 3.004005 secs | 3004005 hour | @ 1 hour 2 mins 3.004005 secs ago | -1 minute | @ 1 hour 2 mins 3.004005 secs ago | -2 second | @ 1 hour 2 mins 3.004005 secs ago | -3.004005 millisecond | @ 1 hour 2 mins 3.004005 secs ago | -3004.005 microsecond | @ 1 hour 2 mins 3.004005 secs ago | -3004005 (10 rows) SELECT extract_duration('epoch', d) FROM func_table; extract_duration ------------------ 3723.004005 -3723.004005 (2 rows) SELECT extract_duration('millennium', d) FROM func_table; ERROR: unit "millennium" not supported for type duration SELECT extract_duration('century', d) FROM func_table; ERROR: unit "century" not supported for type duration SELECT extract_duration('decade', d) FROM func_table; ERROR: unit "decade" not supported for type duration SELECT extract_duration('year', d) FROM func_table; ERROR: unit "year" not supported for type duration SELECT extract_duration('quarter', d) FROM func_table; ERROR: unit "quarter" not supported for type duration SELECT extract_duration('month', d) FROM func_table; ERROR: unit "month" not supported for type duration SELECT extract_duration('week', d) FROM func_table; ERROR: unit "week" not supported for type duration SELECT extract_duration('day', d) FROM func_table; ERROR: unit "day" not supported for type duration -- Casts -- Valid SELECT s, s::duration::interval AS interval, s::duration::interval::duration AS dur FROM (SELECT s FROM input_table UNION ALL SELECT '-' || s FROM input_table); s | interval | dur --------------------------------------------+--------------------------------+-------------------------------- 42 hour | @ 42 hours | @ 42 hours 43 hours | @ 43 hours | @ 43 hours 44 h | @ 44 hours | @ 44 hours 45 minute | @ 45 mins | @ 45 mins 46 minutes | @ 46 mins | @ 46 mins 47 m | @ 47 mins | @ 47 mins 48 second | @ 48 secs | @ 48 secs 49 seconds | @ 49 secs | @ 49 secs 50 s | @ 50 secs | @ 50 secs 51 millisecond | @ 0.051 secs | @ 0.051 secs 52 milliseconds | @ 0.052 secs | @ 0.052 secs 53 ms | @ 0.053 secs | @ 0.053 secs 54 microsecond | @ 0.000054 secs | @ 0.000054 secs 55 microseconds | @ 0.000055 secs | @ 0.000055 secs 56 us | @ 0.000056 secs | @ 0.000056 secs 1 minute 2 h 3 microseconds 4 second 5 ms | @ 2 hours 1 min 4.005003 secs | @ 2 hours 1 min 4.005003 secs -42 hour | @ 42 hours ago | @ 42 hours ago -43 hours | @ 43 hours ago | @ 43 hours ago -44 h | @ 44 hours ago | @ 44 hours ago -45 minute | @ 45 mins ago | @ 45 mins ago -46 minutes | @ 46 mins ago | @ 46 mins ago -47 m | @ 47 mins ago | @ 47 mins ago -48 second | @ 48 secs ago | @ 48 secs ago -49 seconds | @ 49 secs ago | @ 49 secs ago -50 s | @ 50 secs ago | @ 50 secs ago -51 millisecond | @ 0.051 secs ago | @ 0.051 secs ago -52 milliseconds | @ 0.052 secs ago | @ 0.052 secs ago -53 ms | @ 0.053 secs ago | @ 0.053 secs ago -54 microsecond | @ 0.000054 secs ago | @ 0.000054 secs ago -55 microseconds | @ 0.000055 secs ago | @ 0.000055 secs ago -56 us | @ 0.000056 secs ago | @ 0.000056 secs ago -1 minute 2 h 3 microseconds 4 second 5 ms | @ 1 hour 59 mins 4.005003 secs | @ 1 hour 59 mins 4.005003 secs (32 rows) -- Invalid SELECT (interval '1 month')::duration; ERROR: invalid units for duration SELECT (interval '1 day')::duration; ERROR: invalid units for duration SELECT (interval '1 month 2 days 3 hours')::duration; ERROR: invalid units for duration --- Aggregates SELECT avg(s::duration), count(s::duration), max(s::duration), min(s::duration), sum(s::duration) FROM ( SELECT s FROM input_table UNION ALL SELECT '-' || s FROM input_table UNION ALL SELECT NULL ); avg | count | max | min | sum -------------------------+-------+------------+----------------+------------------------- @ 7 mins 30.250313 secs | 32 | @ 44 hours | @ 44 hours ago | @ 4 hours 8.010006 secs (1 row) -- Infinity -- Create helper tables CREATE TEMP TABLE inf_table(d duration); INSERT INTO inf_table VALUES ('infinity'), ('-infinity'), ('999 hours'), ('-999 hours'); -- Input SELECT duration 'infinity'; duration ---------- infinity (1 row) SELECT duration '-infinity'; duration ----------- -infinity (1 row) -- Comparison SELECT t1.d AS a, t2.d AS b, t1.d < t2.d AS lt, t1.d <= t2.d AS le, t1.d > t2.d AS gt, t1.d >= t2.d AS ge, t1.d = t2.d AS eq, t1.d != t2.d AS ne FROM inf_table AS t1, inf_table AS t2 WHERE NOT isfinite(t1.d) OR NOT isfinite(t2.d); a | b | lt | le | gt | ge | eq | ne -----------------+-----------------+----+----+----+----+----+---- infinity | infinity | f | t | f | t | t | f infinity | -infinity | f | f | t | t | f | t infinity | @ 999 hours | f | f | t | t | f | t infinity | @ 999 hours ago | f | f | t | t | f | t -infinity | infinity | t | t | f | f | f | t -infinity | -infinity | f | t | f | t | t | f -infinity | @ 999 hours | t | t | f | f | f | t -infinity | @ 999 hours ago | t | t | f | f | f | t @ 999 hours | infinity | t | t | f | f | f | t @ 999 hours | -infinity | f | f | t | t | f | t @ 999 hours ago | infinity | t | t | f | f | f | t @ 999 hours ago | -infinity | f | f | t | t | f | t (12 rows) -- Negation SELECT d, -d FROM inf_table WHERE NOT isfinite(d); d | ?column? -----------+----------- infinity | -infinity -infinity | infinity (2 rows) -- Addition SELECT duration 'infinity' + duration '42 ms'; ?column? ---------- infinity (1 row) SELECT duration '-infinity' + duration '42 ms'; ?column? ----------- -infinity (1 row) SELECT duration 'infinity' + duration 'infinity'; ?column? ---------- infinity (1 row) SELECT duration 'infinity' + duration '-infinity'; ERROR: duration out of range SELECT duration '-infinity' + duration 'infinity'; ERROR: duration out of range SELECT duration '-infinity' + duration '-infinity'; ?column? ----------- -infinity (1 row) -- Subtraction SELECT duration 'infinity' - duration '42 ms'; ?column? ---------- infinity (1 row) SELECT duration '-infinity' - duration '42 ms'; ?column? ----------- -infinity (1 row) SELECT duration 'infinity' - duration 'infinity'; ERROR: duration out of range SELECT duration 'infinity' - duration '-infinity'; ?column? ---------- infinity (1 row) SELECT duration '-infinity' - duration 'infinity'; ?column? ----------- -infinity (1 row) SELECT duration '-infinity' - duration '-infinity'; ERROR: duration out of range -- Multiplication SELECT duration 'infinity' * 15.6; ?column? ---------- infinity (1 row) SELECT duration 'infinity' * -15.6; ?column? ----------- -infinity (1 row) SELECT duration 'infinity' * 'infinity'::float8; ?column? ---------- infinity (1 row) SELECT duration 'infinity' * '-infinity'::float8; ?column? ----------- -infinity (1 row) SELECT duration 'infinity' * 'nan'::float8; ERROR: duration out of range SELECT duration 'infinity' * 0.0; ERROR: duration out of range SELECT duration '-infinity' * 15.6; ?column? ----------- -infinity (1 row) SELECT duration '-infinity' * -15.6; ?column? ---------- infinity (1 row) SELECT duration '-infinity' * 'infinity'::float8; ?column? ----------- -infinity (1 row) SELECT duration '-infinity' * '-infinity'::float8; ?column? ---------- infinity (1 row) SELECT duration '-infinity' * 'nan'::float8; ERROR: duration out of range SELECT duration '-infinity' * 0.0; ERROR: duration out of range -- Division SELECT duration 'infinity' / 32.1; ?column? ---------- infinity (1 row) SELECT duration 'infinity' / -32.1; ?column? ----------- -infinity (1 row) SELECT duration 'infinity' / 'infinity'::float8; ERROR: duration out of range SELECT duration 'infinity' / '-infinity'::float8; ERROR: duration out of range SELECT duration 'infinity' / 'nan'::float8; ERROR: duration out of range SELECT duration 'infinity' / 0.0; ERROR: division by zero SELECT duration '-infinity' / 32.1; ?column? ----------- -infinity (1 row) SELECT duration '-infinity' / -32.1; ?column? ---------- infinity (1 row) SELECT duration '-infinity' / 'infinity'::float8; ERROR: duration out of range SELECT duration '-infinity' / '-infinity'::float8; ERROR: duration out of range SELECT duration '-infinity' / 'nan'::float8; ERROR: duration out of range SELECT duration '-infinity' / 0.0; ERROR: division by zero -- date_trunc SELECT f, d, date_trunc(f, d) FROM valid_fields, inf_table WHERE NOT isfinite(d); f | d | date_trunc -------------+-----------+------------ hour | infinity | infinity minute | infinity | infinity second | infinity | infinity millisecond | infinity | infinity microsecond | infinity | infinity hour | -infinity | -infinity minute | -infinity | -infinity second | -infinity | -infinity millisecond | -infinity | -infinity microsecond | -infinity | -infinity (10 rows) SELECT date_trunc('epoch', duration 'infinity'); ERROR: unit "epoch" not recognized for type duration SELECT date_trunc('millennium', duration 'infinity'); ERROR: unit "millennium" not supported for type duration SELECT date_trunc('century', duration 'infinity'); ERROR: unit "century" not supported for type duration SELECT date_trunc('decade', duration 'infinity'); ERROR: unit "decade" not supported for type duration SELECT date_trunc('year', duration 'infinity'); ERROR: unit "year" not supported for type duration SELECT date_trunc('quarter', duration 'infinity'); ERROR: unit "quarter" not supported for type duration SELECT date_trunc('month', duration 'infinity'); ERROR: unit "month" not supported for type duration SELECT date_trunc('week', duration 'infinity'); ERROR: unit "week" not supported for type duration SELECT date_trunc('day', duration 'infinity'); ERROR: unit "day" not supported for type duration SELECT date_trunc('epoch', duration '-infinity'); ERROR: unit "epoch" not recognized for type duration SELECT date_trunc('millennium', duration '-infinity'); ERROR: unit "millennium" not supported for type duration SELECT date_trunc('century', duration '-infinity'); ERROR: unit "century" not supported for type duration SELECT date_trunc('decade', duration '-infinity'); ERROR: unit "decade" not supported for type duration SELECT date_trunc('year', duration '-infinity'); ERROR: unit "year" not supported for type duration SELECT date_trunc('quarter', duration '-infinity'); ERROR: unit "quarter" not supported for type duration SELECT date_trunc('month', duration '-infinity'); ERROR: unit "month" not supported for type duration SELECT date_trunc('week', duration '-infinity'); ERROR: unit "week" not supported for type duration SELECT date_trunc('day', duration '-infinity'); ERROR: unit "day" not supported for type duration -- date_part SELECT f, d, date_part(f, d) FROM valid_fields, inf_table WHERE NOT isfinite(d); f | d | date_part -------------+-----------+----------- hour | infinity | Infinity minute | infinity | second | infinity | millisecond | infinity | microsecond | infinity | hour | -infinity | -Infinity minute | -infinity | second | -infinity | millisecond | -infinity | microsecond | -infinity | (10 rows) SELECT date_part('epoch', duration 'infinity'); date_part ----------- Infinity (1 row) SELECT date_part('millennium', duration 'infinity'); ERROR: unit "millennium" not supported for type duration SELECT date_part('century', duration 'infinity'); ERROR: unit "century" not supported for type duration SELECT date_part('decade', duration 'infinity'); ERROR: unit "decade" not supported for type duration SELECT date_part('year', duration 'infinity'); ERROR: unit "year" not supported for type duration SELECT date_part('quarter', duration 'infinity'); ERROR: unit "quarter" not supported for type duration SELECT date_part('month', duration 'infinity'); ERROR: unit "month" not supported for type duration SELECT date_part('week', duration 'infinity'); ERROR: unit "week" not supported for type duration SELECT date_part('day', duration 'infinity'); ERROR: unit "day" not supported for type duration SELECT date_part('epoch', duration '-infinity'); date_part ----------- -Infinity (1 row) SELECT date_part('millennium', duration '-infinity'); ERROR: unit "millennium" not supported for type duration SELECT date_part('century', duration '-infinity'); ERROR: unit "century" not supported for type duration SELECT date_part('decade', duration '-infinity'); ERROR: unit "decade" not supported for type duration SELECT date_part('year', duration '-infinity'); ERROR: unit "year" not supported for type duration SELECT date_part('quarter', duration '-infinity'); ERROR: unit "quarter" not supported for type duration SELECT date_part('month', duration '-infinity'); ERROR: unit "month" not supported for type duration SELECT date_part('week', duration '-infinity'); ERROR: unit "week" not supported for type duration SELECT date_part('day', duration '-infinity'); ERROR: unit "day" not supported for type duration -- extract_duration SELECT f, d, extract_duration(f, d) FROM valid_fields, inf_table WHERE NOT isfinite(d); f | d | extract_duration -------------+-----------+------------------ hour | infinity | Infinity minute | infinity | second | infinity | millisecond | infinity | microsecond | infinity | hour | -infinity | -Infinity minute | -infinity | second | -infinity | millisecond | -infinity | microsecond | -infinity | (10 rows) SELECT extract_duration('epoch', duration 'infinity'); extract_duration ------------------ Infinity (1 row) SELECT extract_duration('millennium', duration 'infinity'); ERROR: unit "millennium" not supported for type duration SELECT extract_duration('century', duration 'infinity'); ERROR: unit "century" not supported for type duration SELECT extract_duration('decade', duration 'infinity'); ERROR: unit "decade" not supported for type duration SELECT extract_duration('year', duration 'infinity'); ERROR: unit "year" not supported for type duration SELECT extract_duration('quarter', duration 'infinity'); ERROR: unit "quarter" not supported for type duration SELECT extract_duration('month', duration 'infinity'); ERROR: unit "month" not supported for type duration SELECT extract_duration('week', duration 'infinity'); ERROR: unit "week" not supported for type duration SELECT extract_duration('day', duration 'infinity'); ERROR: unit "day" not supported for type duration SELECT extract_duration('epoch', duration '-infinity'); extract_duration ------------------ -Infinity (1 row) SELECT extract_duration('millennium', duration '-infinity'); ERROR: unit "millennium" not supported for type duration SELECT extract_duration('century', duration '-infinity'); ERROR: unit "century" not supported for type duration SELECT extract_duration('decade', duration '-infinity'); ERROR: unit "decade" not supported for type duration SELECT extract_duration('year', duration '-infinity'); ERROR: unit "year" not supported for type duration SELECT extract_duration('quarter', duration '-infinity'); ERROR: unit "quarter" not supported for type duration SELECT extract_duration('month', duration '-infinity'); ERROR: unit "month" not supported for type duration SELECT extract_duration('week', duration '-infinity'); ERROR: unit "week" not supported for type duration SELECT extract_duration('day', duration '-infinity'); ERROR: unit "day" not supported for type duration -- Casts SELECT d, d::INTERVAL AS interval, d::INTERVAL::duration AS dur FROM inf_table WHERE NOT isfinite(d); d | interval | dur -----------+-----------+----------- infinity | infinity | infinity -infinity | -infinity | -infinity (2 rows) -- Aggregates SELECT avg(d), max(d), min(d), sum(d) FROM inf_table WHERE d != duration 'infinity'; avg | max | min | sum -----------+-------------+-----------+----------- -infinity | @ 999 hours | -infinity | -infinity (1 row) SELECT avg(d), max(d), min(d), sum(d) FROM inf_table WHERE d != duration '-infinity'; avg | max | min | sum ----------+----------+-----------------+---------- infinity | infinity | @ 999 hours ago | infinity (1 row) SELECT max(d), min(d) FROM inf_table; max | min ----------+----------- infinity | -infinity (1 row) SELECT avg(d) FROM inf_table; ERROR: duration out of range SELECT sum(d) FROM inf_table; ERROR: duration out of range -- Overflow SELECT duration '9223372036854775807 us'; duration ---------- infinity (1 row) SELECT duration '-9223372036854775808 us'; duration ----------- -infinity (1 row) -- Inserts and indexes DROP SCHEMA IF EXISTS regress CASCADE; NOTICE: schema "regress" does not exist, skipping CREATE SCHEMA regress; CREATE TABLE regress.t (d1 duration, d2 duration); CREATE INDEX idx1 ON regress.t USING BTREE (d1); CREATE INDEX idx2 ON regress.t USING HASH (d2); INSERT INTO regress.t VALUES ('1 s 6 m', '5 us'), ('500ms 4 h', '42 seconds 3 us'), ('666 hours', '999 min'); SELECT * FROM regress.t; d1 | d2 --------------------+-------------------- @ 6 mins 1 sec | @ 0.000005 secs @ 4 hours 0.5 secs | @ 42.000003 secs @ 666 hours | @ 16 hours 39 mins (3 rows) SELECT d1 FROM regress.t WHERE d1 > '1 hour'; d1 -------------------- @ 4 hours 0.5 secs @ 666 hours (2 rows) SELECT d2 FROM regress.t WHERE d2 = '42 seconds 3 microseconds'; d2 ------------------ @ 42.000003 secs (1 row) DROP SCHEMA regress CASCADE; NOTICE: drop cascades to table regress.t