PostgreSQL interval

See also

A Comprehensive Look at PostgreSQL Interval Data Type

See also

Summary: in this tutorial, you will learn about the PostgreSQL interval data type and how to manipulate interval values.

Introduction to PostgreSQL interval data type

The interval data type allows you to store and manipulate a period of time in years, months, days, hours, minutes, seconds, etc.

The following illustrates the interval type: 

@ interval [ fields ] [ (p) ]

An interval value requires 16 bytes storage size that can store a period with the allowed range is from -178,000,000 years to 178,000,000 years.

In addition, an interval value can have an optional precision value p with the permitted range is from 0 to 6.

The precision p is the number of fraction digits retained in the second fields.

The at sign ( @) is optional therefore you can omit it.

The following examples show some interval values: 

interval '2 months ago';
interval '3 hours 20 minutes';

Internally, PostgreSQL stores interval values as months, days, and seconds.

The months and days values are integers while the seconds can field can have fractions.

The interval values are very useful when doing date or time arithmetic.

For example, if you want to know the time of 3 hours 2 minutes ago at the current time of last year, you can use the following statement: 

SELECT
   now(),
   now() - INTERVAL '1 year 3 hours 20 minutes'  AS "3 hours 20 minutes ago of last year";

PostgreSQL Data Types: Date, Timestamp, and Time Zones by Tapoueh

See also

PostgreSQL implements an interval data type along with the time, date and timestamptz data types. An interval describes a duration, like a month or two weeks, or even a millisecond: 

set intervalstyle to postgres;

select interval '1 month',
       interval '2 weeks',
       2 * interval '1 week',
       78389 * interval '1 ms';

 interval | interval | ?column? |   ?column?
----------+----------+----------+--------------
 1 mon    | 14 days  | 14 days  | 00:01:18.389

Several intervalstyle values are possible, and the setting postgres_verbose is quite nice for interactive psql sessions: 

set intervalstyle to postgres_verbose;

select interval '1 month',
       interval '2 weeks',
       2 * interval '1 week',
       78389 * interval '1 ms';

interval | interval  | ?column?  |      ?column?
----------+-----------+-----------+---------------------
 @ 1 mon  | @ 14 days | @ 14 days | @ 1 min 18.389 secs

How long is a month? Well, it depends on which month, and PostgreSQL knows that: 

select d::date as month,
       (d + interval '1 month' - interval '1 day')::date as month_end,
       (d + interval '1 month')::date as next_month,
       (d + interval '1 month')::date - d::date as days

  from generate_series(
                       date '2020-01-01',
                       date '2020-12-01',
                       interval '1 month'
                      )
       as t(d);

When you attach an interval to a date or timestamp in PostgreSQL then the number of days in that interval adjusts to the specific calendar entry you’ve picked.

Otherwise, an interval of a month is considered to be 30 days.

Here we see that computing the last day of February is very easy: 

  month    | month_end  | next_month | days
------------+------------+------------+------
 2020-01-01 | 2020-01-31 | 2020-02-01 |   31
 2020-02-01 | 2020-02-29 | 2020-03-01 |   29
 2020-03-01 | 2020-03-31 | 2020-04-01 |   31
 2020-04-01 | 2020-04-30 | 2020-05-01 |   30
 2020-05-01 | 2020-05-31 | 2020-06-01 |   31
 2020-06-01 | 2020-06-30 | 2020-07-01 |   30
 2020-07-01 | 2020-07-31 | 2020-08-01 |   31
 2020-08-01 | 2020-08-31 | 2020-09-01 |   31
 2020-09-01 | 2020-09-30 | 2020-10-01 |   30
 2020-10-01 | 2020-10-31 | 2020-11-01 |   31
 2020-11-01 | 2020-11-30 | 2020-12-01 |   30
 2020-12-01 | 2020-12-31 | 2021-01-01 |   31
(12 lignes)