pg_duration

pg_duration : data type for representing durations

Overview

Packages

Source

pig build pkg pg_duration;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_duration;		# install via package name, for the active PG version

pig install pg_duration -v 18;   # install for PG 18
pig install pg_duration -v 17;   # install for PG 17

Create this extension with:

CREATE EXTENSION pg_duration;

Usage

pg_duration: ISO 8601 duration type for PostgreSQL

The pg_duration extension provides a duration type for storing elapsed time as microseconds in 8 bytes, simpler and more consistently comparable than the built-in interval type.

CREATE EXTENSION pg_duration;

Data Type

The duration type represents absolute elapsed time without calendar components (no months or days). Valid input accepts any PostgreSQL interval syntax that does not exceed hourly units.

SELECT '01:30:00'::duration;
SELECT '2 hours 30 minutes'::duration;

Operators

• Arithmetic: +, - between durations; *, / by float8; unary -
• Comparison: <, <=, >, >=, =, <>

Functions

-- Construct from components
SELECT make_duration(hours => 2, mins => 30, secs => 15.5);

-- Check for infinity
SELECT isfinite('01:00:00'::duration);

-- Truncate to precision
SELECT date_trunc('hour', '02:45:30'::duration);

-- Extract subfield
SELECT date_part('minute', '02:45:30'::duration);
SELECT extract_duration('hour', '02:45:30'::duration);

Type Conversions

Durations cast implicitly to interval. Converting interval to duration requires explicit casting.

Aggregates and Indexing

Standard aggregates (avg, count, max, min, sum) and both B-tree and hash indexes are supported.