Contents
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