PostgreSQL Interval

PostgreSQL is a popular relational database management system. It’s an open-source software which is popular for its stability and support for various open technical standards. It’s actively maintained by developers from all over the world.

In this guide, we will work on INTERVAL in PostgreSQL.

Prerequisites:

To perform the steps that are demonstrated in this guide, you will need the following components:

• A properly-configured Linux system. Learn more about installing Ubuntu in VirtualBox.
• A proper installation of PostgreSQL. Learn more about installing PostgreSQL on Ubuntu.

INTERVAL in PostgreSQL

To store and manage the time periods (minutes, seconds, years, etc.), PostgreSQL offers the INTERVAL data type.

• The size of INTERVAL is 16 bytes.
• It can store values from -178000000 years to 178000000 years.
• Different INTERVAL styles are available, for example, postgres (default), postgres_verbose, sql_standard, iso_8601, etc.
• Arithmetic operations are possible.

The INTERVAL type is as follows:

Here:

• An INTERVAL value is permitted to have a precision value p where the value of p can range from 0 to 6.
• The “@” sign is optional, therefore can be omitted safely.

INTERVAL Formats

Where:

• quantity: A number that can accept “+” or “-“.
• unit: Various time units are supported. For example: decade, century, month, day, week, hour, second, and abbreviations (d, m, y, etc.). Plural forms are also supported (days, months, etc.).

This is the postgres_verbose format that’s common for INTERVAL output formats.

ISO 8601

The structure of ISO 8601 format with designators is as follows:

Here:

• The value starts with “P”.
• The value following the “T” denotes the time of the day.

The following unit abbreviations are available in ISO 8601:

• Y: years
• M: months (within the date section)
• W: weeks
• D: days
• H: hours
• M: minutes (within the time section)
• S: seconds

Here’s an INTERVAL example in ISO 8601 format (with designators):

There’s an alternate format of ISO 8601. The structure is as follows:

Here’s the same INTERVAL example in ISO 8601 alternate format:

Using the INTERVAL Values

In this section, we will demonstrate the various ways of using the INTERVAL values in PostgreSQL queries.

Basic Usage
From the PostgreSQL shell, run the following query:

SELECT

Here:

• The NOW() function returns the current date and time.
• We subtract “1 year 3 hours 20 minute”’ from NOW() to get the desired value.
• Using the AS statement, we specify a name for the second column of the output.

INTERVAL Output Formats

In PostgreSQL, we can specify the output format of an INTERVAL value using the following command:

There are a couple of available formats:

• sql_standard
• postgres
• postgres_verbose
• iso_8601

Let’s put this command into action. The following queries show the INTERVAL output in different formats:

INTERVAL Arithmetic Operators

It’s possible to apply the arithmetic operations (+, -, *) on the INTERVAL values. The following queries demonstrate this feature:

SELECT

SELECT

SELECT

Converting INTERVAL to String

With the help of the TO_CHAR() function, we can convert the value of INTERVAL to a string. The structure of TO_CHAR() is as follows:

Here:

• The first argument is the INTERVAL value to be transformed. It can be in any INTERVAL format.
• The second argument describes the format of the output.

The following query demonstrates a simple example of converting an INTERVAL in ISO 8601 format to a simple string:

Data Extraction from INTERVAL

With the help of the EXTRACT() function, we can extract the content of a specific field from an INTERVAL value. The command structure of EXTRACT() in tandem with INTERVAL is as follows:

Here:

• field: The field to be extracted from INTERVAL. For example: seconds, minutes, hours, date, month, year, etc.
• interval: The INTERVAL value.

Let’s put the EXTRACT() function into action. In the following example, we extract the MINUTE field from an INTERVAL value:

SELECT

INTERVAL Value Adjustment

The justify_days() and justify_hours() functions can convert the intervals of 30 days to 1 month and 24 hours to 1 day, respectively.

The following query demonstrates the usage of these functions:

SELECT

Conclusion

We went over the INTERVAL feature in PostgreSQL. This guide elaborates on various supported INTERVAL formats. It also showcases the various ways of implementing the INTERVAL in queries. Finally, we also explored the various PostgreSQL functions that translate/transform the INTERVAL value in the specified formats.

Need to interact with PostgreSQL over a network? There are various available PostgreSQL GUIs that can connect to a remote server and manage it effortlessly. Besides the built-in functions, PostgreSQL also supports the user-defined functions.