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

To define a PostgreSQL INTERVAL type value, use the following method:

@ INTERVAL [ fields ] [ (p) ]

Explanation:

The @ is optional.
The fields defines a time period. You can use the following two input formats:
- quantity unit [quantity unit...] [direction]
  
  Where, the quantity is a number, with sign possibly; The unit is a value in microsecond, millisecond, second, minute, hour, day, week, month, year,, decade, century, millennium or their abbreviated or plural forms; The direction can be ago or empty.
  
  E.g: INTERVAL '1 years 2 months 3 days 4 hours 5 minutes 6 seconds'
- ISO 8601 time interval format
  
  P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]
  
  or
  
  P [ years-months-days ] [ T hours:minutes:seconds ]
  
  where unit you can use the values from the following table:
  
  abbreviation meaning
  
  Y year
  
  M month (date part)
  
  W week
  
  D sky
  
  H Hour
  
  M minutes (time part)
  
  S second
  
  E.g:
```
INTERVAL 'P1Y2M3DT4H5M6S'
INTERVAL 'P1-2-3T4:5:6'
```
The p is the precision of fractional seconds. The allowed range is from 0 to 6.

abbreviation	meaning
`Y`	year
`M`	month (date part)
`W`	week
`D`	sky
`H`	Hour
`M`	minutes (time part)
`S`	second

For example, interval '1 years 2 months 3 days' means 1 year, 2 months and 3 days.

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

sql_standard

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

PostgreSQL INTERVAL output format