pg_duration

pg_duration is a PostgreSQL extension that adds a duration data type to PostgreSQL. duration allows users to store the total time of some event in their databases. duration is very similar to the builtin interval type, except duration does not have a months or days component, only a microsecond component.

pg_duration is compatible with PostgreSQL versions 17 and above.

Installation

Before beginning, make sure that the PostgreSQL server dev libraries are downloaded to your machine: https://www.postgresql.org/download.

First build pg_duration:

make
make install

Note: You may need to run make install under sudo.

Then conntect to a database as a superuser and run:

CREATE EXTENSION pg_duration;

Usage

Input

All valid interval input that doesn't specify units larger than hours, is valid duration input.

Output

duration output is the same as interval output, without units larger than hours.

Operators

Operator	Description	Example
duration + duration -> duration	Add durations	duration '5 sec' + duration '10 min' -> 00:10:05
duration - duration -> duration	Subtract durations	duration '6 hours' - duration '15 min' -> 05:45:00
- duration -> duration	Negate a duration	- duration '450 milliseconds' -> -00:00:00.45
duration * float8 -> duration	Multiply a duration	duration '3 hours' * 2.5 -> 07:30:00
duration / float8 -> duration	Divide a duration	duration '3 hours' / 2.5 -> 01:12:00
duration < duration -> boolean	Less than	duration '10 min' < duration '1 hour' -> t
duration <= duration -> boolean	Less than or equal	duration '10 min' <= duration '1 hour' -> t
duration > duration -> boolean	Greater than	duration '10 min' > duration '1 hour' -> f
duration >= duration -> boolean	Greater than or equal	duration '10 min' >= duration '1 hour' -> f
duration = duration -> boolean	Equal	duration '10 min' = duration '1 hour' -> f
duration <> duration -> boolean	Not equal	duration '10 min' <> duration '1 hour' -> t

Functions

Function	Description	Example
make_duration([hours int [, mins int [, secs double precision ]]]) -> duration	Create duration from hours, minutes, and seconds fields, each of which can default to zero	make_duration(12) -> 12:00:00
isfinite(duration) -> boolean	Test for finite duration (not +/-infinity)	isfinite(duration '1 hour') -> true
date_trunc(text, duration) -> duration	Truncate to specified precision; see date_trunc	date_trunc('second', duration '3 hours 40 minutes 5 seconds 60 ms') -> 03:40:05
date_part(text, duration) -> double precision	Get duration subfield (equivalent to extract_duration); see date_part	date_part('minute', duration '1 hour 2 minutes 3 seconds') -> 2
extract_duration(text, duration) -> numeric	Get duration subfield; see extract	extract_duration('second', duration '1 hour 2 minutes 3 seconds') -> 3.004

Casts

Source Type	Target Type	Cast Type
duration	interval	implicit
interval	duration	explicit

Aggregates

Aggregate	Return Type	Description
avg	duration	The average (arithmetic mean) of all non-null input values
count	bigint	Number of input rows for which the value is not null
max	duration	Maximum value across all non-null input values
min	duration	Minimum value across all non-null input values
sum	duration	Sum across all non-null input values

Supported Indexes

The duration type supports the following indexes

• BTREE
• HASH

Rationale

Why not just use the interval type? For starters, the interval type is 16 bytes while the duration type is only 8 bytes. More importantly, intervals only tell us the time between two events, not the absolute time of some event. For example, how many hours is Interval '2 months 15 days'? To answer that you'd need to know if the months had 28, 29, 30, or 31 days and if the days had 23, 24, or 25 hours. durations on the other hand can always be compared to other durations and return a meaningful answer.