PostgreSQL INTERVAL Data Type

In PostgreSQL, INTERVAL types are used to store time interval values, such as N years, N months, N days, N hours, N minutes, and N seconds. A time interval is not a time point, but a time period.

The PostgreSQL INTERVAL type occupies 16 bytes of storage, and its values range from -178000000 years to 178000000 years.

PostgreSQL `INTERVAL` Syntax

The INTERVAL type declaration syntax is:

INTERVAL [ fields ] [ (p) ]

fields is optional and restricts which interval fields are stored, such as YEAR TO MONTH or DAY TO SECOND. p is an optional fractional-second precision from 0 to 6; when both fields and p are specified, the fields must include SECOND.

The optional @ prefix belongs to verbose interval input, not to the type declaration. For example, both of these interval literals are valid:

INTERVAL '1 day'
INTERVAL '@ 1 day'

Interval input formats

The verbose input form uses one or more quantity/unit pairs and an optional ago direction:

SELECT INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds';

quantity can be signed. Units include microseconds, milliseconds, seconds, minutes, hours, days, weeks, months, years, decades, centuries, and millennia, plus their abbreviations and plural forms.

You can also use either ISO 8601 interval format:

P quantity unit [ quantity unit ... ] [ T [ quantity unit ... ] ]
P [ years-months-days ] [ T hours:minutes:seconds ]

The M abbreviation means months before T and minutes after T. For example:

SELECT INTERVAL 'P1Y2M3DT4H5M6S';
SELECT INTERVAL 'P1-2-3T4:5:6';

Abbreviation	Meaning
`Y`	Year
`M` before `T`	Month (date part)
`W`	Week
`D`	Day
`H`	Hour
`M` after `T`	Minute (time part)
`S`	Second

PostgreSQL `INTERVAL` output format

The output format for PostgreSQL INTERVAL values can be set to sql_standard, postgres(default), postgres_verbose, or iso_8601.

You can use the SET intervalstyle command to set the output format of the INTERVAL type.

The following examples show different output formats of 6 years 5 months 4 days 3 hours 2 minutes 1 second :

sql_standard

SET intervalstyle = 'sql_standard';
SELECT INTERVAL 'P1Y2M3DT4H5M6S';

    interval
------------------
 +1-2 +3 +4:05:06

postgres

SET intervalstyle = 'postgres';
SELECT INTERVAL 'P1Y2M3DT4H5M6S';

           interval
-------------------------------
 1 year 2 mons 3 days 04:05:06

postgres_verbose

SET intervalstyle = 'postgres_verbose';
SELECT INTERVAL 'P1Y2M3DT4H5M6S';

                   interval
----------------------------------------------
 @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs

iso_8601

SET intervalstyle = 'iso_8601';
SELECT INTERVAL 'P1Y2M3DT4H5M6S';

    interval
----------------
 P1Y2M3DT4H5M6S

PostgreSQL INTERVAL operators and functions

Arithmetic Operations

You can apply arithmetic operators (+, -) to interval values, for example:

SELECT INTERVAL '1 day 12h 50m' + INTERVAL '10m';

    ?column?
----------------
 1 day 13:00:00

You can apply arithmetic operators (+, -) to time and interval values, for example:

SELECT
  current_timestamp "Now",
  current_timestamp - INTERVAL '10m'
    AS "Ten minutes ago";

 -[ RECORD 1 ]---+------------------------------
Now             | 2022-09-02 16:08:29.933843+08
Ten minutes ago | 2022-09-02 15:58:29.933843+08

Extract parts from intervals

You can use the date_part() function to extract specified fields from an interval value. The following statement extracts the number of days, hours, and minutes from the interval value:

SELECT
  date_part('day', INTERVAL '2 days 10 minutes') "days",
  date_part('hour', INTERVAL '2 days 10 minutes') "hours",
  date_part('minutes', INTERVAL '2 days 10 minutes') "minutes";

 days | hours | minutes
------+-------+---------
    2 |     0 |      10

Convert time value

PostgreSQL provides three functions for converting time values:

justify_days() Convert days over 30 days to months.
justify_hours() Convert hours over 24 hours to days.
justify_interval() Convert hours over 24 hours to days, and days over 30 days to months.

Example 1:

SELECT justify_days(INTERVAL '1 year 40 days');

     justify_days
----------------------
 1 year 1 mon 10 days

Example 2:

SELECT justify_hours(INTERVAL '60 hours');

  justify_hours
-----------------
 2 days 12:00:00

Example 3:

SELECT justify_interval(INTERVAL '6000 hours');

 justify_interval
------------------
 8 mons 10 days

Conclusion

In PostgreSQL, INTERVAL types are used to store interval values representing time periods.

PostgreSQL INTERVAL Syntax