A Comprehensive Look at PostgreSQL Interval Data Type

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:

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:

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:

Let’s see how to format interval values for input and output.

PostgreSQL interval input format

PostgreSQL provides you with the following verbose syntax to write the interval values:

• quantity is a number, sign + or - is also accepted
• unit can be any of millennium, century, decade, year, month, week, day, hour, minute, second, millisecond, microsecond, or abbreviation (y, m, d, etc.,) or plural forms (months, days, etc.).
• direction can be ago or empty string ''

This format is called postgres_verbose which is also used for the interval output format. The following examples illustrate some interval values that use the verbose syntax:

ISO 8601 interval format

In addition to the verbose syntax above, PostgreSQL allows you to write the interval values using ISO 8601 time intervals in two ways: format with designators and alternative format.

The ISO 8601 format with designators is like this:

In this format, the interval value must start with the letter P. The letter T is for determining time-of-day unit.

The following table illustrates the ISO 8601 interval unit abbreviations:

Note that M can be months or minutes depending on whether it appears before or after the letter T.

For example, the interval of 6 years 5 months 4 days 3 hours 2 minutes 1 second can be written in the ISO 8601 designators format as follows:

The alternative form of ISO 8601 is:

It also must start with the letter P, and the letter T separates the date and time parts of the interval value. For example, the interval of 6 years 5 months 4 days 3 hours 2 minutes 1 second can be written in the ISO 8601 alternative form as:

PostgreSQL interval output format

The output style of interval values is set by using the SET intervalstyle command, for example:

PostgreSQL provides four output formats: sql standard, postgres, postgresverbose, and iso_8601. PostgresQL uses the postgres style by default for formatting the interval values.

The following represents the interval of 6 years 5 months 4 days 3 hours 2 minutes 1 second in the four styles:

PostgreSQL interval related operators and functions

Interval operators

You can apply the arithmetic operator ( +, -, *, etc.,) to the interval values, for examples:

Converting PostgreSQL interval to string

To convert an interval value to string, you use the to_char function.

The to_char function takes the first argument as an interval value, the second one as the pattern, and return a string that represents the interval in the specified pattern.

See the following example:

It returned the following output:

Extracting data from a PostgreSQL interval

To extract data such as year, month, date, etc., from an interval, you use the extract function.

The field can be year, month, date, hour, minutes, etc., that you want to extract from the interval. The extract function returns a value of type double precision.

See the following example:

In this example, we extracted the minute from the interval of 5 hours 21 minutes and it returned 21 as expected:

Adjusting interval values

PostgreSQL provides two functions justifydays and  justifyhours that allow you to adjust the interval of 30-day as one month and the interval of 24-hour as one day:

In addition, the justify_interval function adjusts interval using justifydays and  justifyhours with additional sign adjustments:

In this tutorial, you have learned about the PostgreSQL interval data type and how to manipulate interval values.