pg_duration

Description

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.

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] 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] date_part('minute', duration '1 hour 2 minutes 3 seconds') -> 2
extract_duration(text, duration) -> numeric Get duration subfield; see [extract][date_part] 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