pg_duration

Overview

Version

Build

You can build the RPM / DEB packages for pg_duration using pig build:

pig build pkg pg_duration         # build RPM / DEB packages

Install

You can install pg_duration directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_duration;          # Install for current active PG version

pig ext install -y pg_duration -v 18  # PG 18
pig ext install -y pg_duration -v 17  # PG 17

dnf install -y pg_duration_18       # PG 18
dnf install -y pg_duration_17       # PG 17

apt install -y postgresql-18-pg-duration   # PG 18
apt install -y postgresql-17-pg-duration   # PG 17

Create Extension:

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.