PostgreSQL INTERVAL Data Type

Summary: In this tutorial, you’ll learn how to manipulate interval values using the PostgreSQL INTERVAL data type.

PostgreSQL INTERVAL Data Type Overview #

In PostgreSQL, intervals are time durations such as 2 days, 3 hours, 1 year 6 months.

You use the INTERVAL data type to store intervals. The interval type can store intervals in years, months, hours, minutes, seconds, and milliseconds.

Here’s the syntax for defining a literal value:

INTERVAL 'value unit'Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

value is a number that indicates a duration.
unit is the time unit, which can be years, months, hours, minutes, seconds, milliseconds, microseconds, decade, century, and millennium.

An interval can consist of multiple units like '1 days 2 hours'.

PostgreSQL INTERVAL Data Type Examples #

The following statement shows how to create an interval of 2 days:

SELECT
  INTERVAL '2 days';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 interval
----------
 2 daysCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement shows how to combine multiple units in a single interval:

SELECT
  INTERVAL '2 days 5 hours 30 minutes';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    interval
-----------------
 2 days 05:30:00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you use a decimal value, PostgreSQL will convert it to the corresponding unit:

SELECT
  INTERVAL '1.5 hours 30 minutes';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 interval
----------
 02:00:00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, PostgreSQL converts 1.5 hours to 1 hour and 30 minutes and adds 30 minutes, resulting in 2 hours.

The following example uses the decade unit for an interval:

SELECT
  INTERVAL '1 decade';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 interval
----------
 10 yearsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Casting a String into an Interval #

If you have a valid interval string, you explicitly cast it into an INTERVAL using the cast operator (::):

'value'::INTERVALCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example:

SELECT
  '1 day 10 hours'::INTERVAL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    interval
----------------
 1 day 10:00:00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Creating Intervals with Precision #

PostgreSQL allows you to specify precision specifications for fractional seconds explicitly:

INTERVAL second(p)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, p is the number of fractional digits you want to retain in the interval.

For example, the following creates an interval with two numbers for fractional seconds:

SELECT
  INTERVAL '1.123456 seconds'::INTERVAL(2);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  interval
-------------
 00:00:01.12Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Creating Tables with Interval Columns #

First, create a table that has an INTERVAL column:

CREATE TABLE subscription_plans (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  duration INTERVAL NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, insert some rows into the subscription_plans table:

INSERT INTO
  subscription_plans (name, duration)
VALUES
  ('6 months', INTERVAL '6 months'),
  ('1 year', INTERVAL '1 year'),
  ('3 years', INTERVAL '3 years')
RETURNING
  *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |   name   | duration
----+----------+----------
  1 | 6 months | 6 mons
  2 | 1 year   | 1 year
  3 | 3 years  | 3 yearsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, retrieve plans with the duration 1 year and up:

SELECT * FROM subscription_plans
WHERE duration >= INTERVAL '1 year';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 id |  name   | duration
----+---------+----------
  2 | 1 year  | 1 year
  3 | 3 years | 3 yearsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Calculating with Intervals #

You can add, subtract, multiply, and divide intervals. For example, the following adds 7 days to the current timestamp:

SELECT
  NOW() + INTERVAL '7 days' next_week;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The following example subtracts 3 days from the current timestamp:

SELECT
  NOW() - INTERVAL '3 days' three_days_ago;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

To multiply a timestamp, you use the * operator:

SELECT INTERVAL '1 hour' * 24 result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  result
----------
 24:00:00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

And you can use the division operator (/) to divide an interval:

SELECT
  INTERVAL '1 day' / 24 result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  result
----------
 01:00:00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Extracting Components from Intervals #

To extract interval fields, you use the EXTRACT function. For example, the following statement extracts day from an interval:

SELECT
  EXTRACT(
    DAY
    FROM
      INTERVAL '2 days 5 hours'
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 extract
---------
       2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Adjusting Intervals #

PostgreSQL provides three functions for adjusting intervals:

JUSTIFY_HOURS: adjust excess hours into days.
JUSTIFY_DAYS: adjust excess days into months while handling leap year accurately.
JUSTIFY_INTERVAL: adjusts months to years and days to months.

For example, the following statement uses the JUSTIFY_HOURS to adjust an interval:

SELECT JUSTIFY_HOURS(INTERVAL '26 hours');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 justify_hours
----------------
 1 day 02:00:00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement uses the JUSTIFY_DAYS to adjust excess days into months:

SELECT
  JUSTIFY_DAYS(INTERVAL '75 days');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  justify_days
----------------
 2 mons 15 daysCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following example uses the JUSTIFY_INTERVAL to adjust excess months to years and days to months:

SELECT
  JUSTIFY_INTERVAL(INTERVAL '13 months 30 days');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 justify_interval
------------------
 1 year 2 monsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

Use INTERVAL 'value unit' to create an interval.
Use the cast operator (::) to cast a string into an interval.
Use the EXTRACT function to extract a component from an interval.
Use the JUSTIFY_HOURS, JUSTIFY_DAYS, and JUSTIFY_INTERVAL functions to adjust intervals.
Perform interval arithmetic operations using the operators (+, -, *, /).

Quiz #

Was this tutorial helpful ?