A PostgreSQL INTERVAL data type represents a duration of time, such as the time between two events, an event's duration, etc. It takes 16 bytes of storage and ranges between -178000000 to +178000000 years. It stores a period of time as a single value(e.g., years, months, days, hours, minutes, etc.).

Users can also specify the precision of the INTERVAL data type while defining a table’s column. For instance, a column defined as an interval(4) will store the intervals with up to 4 digits of precision. The precision value is applied to the seconds' field only.

This blog post will give you an in-depth overview of how to use the INTERVAL data type in Postgres. So, let’s start!

How to Use INTERVAL Data Type in PostgreSQL?

The below snippet demonstrates the interval data type:

INTERVAL [field] [(p)]

In the above snippet, the “field” represents a time period, such as a year, month, day, hour, etc. While “p” represents a precision value.

Let’s put it into practice for a profound understanding!

Example 1: Creating a Column With INTERVAL Data Type

This example states how to create a column with INTERVAL data type in Postgres:

CREATE TABLE sample_tbl(
article_title TEXT,
article_published INTERVAL
);

Now execute the “\d” command to describe the table details:

\d sample_tbl;

The output snippet verifies that a table named “sample_tbl” has been created with an INTERVAL data type column.

Example 2: Inserting Interval into a Column

Let’s learn how to insert intervals into Postgres tables via the INSERT command:

INSERT INTO sample_tbl(article_title, article_published)
VALUES(‘Count Unique Records’, ‘2 Years 3 Months 17 Days’);

You can see the inserted entries via the “SELECT” command:

SELECT * FROM sample_tbl;

The desired record has been inserted into the “sample_tbl” successfully.

Example 3: Adding a Specific INTERVAL to Current Date

In the following code snippet, we will show you how to add an INTERVAL to the current date:

SELECT CURRENT_DATE, 
CURRENT_DATE + INTERVAL '2 Years 3 Months 17 Days' AS "Current Date After 2 Years 3 Months 17 Days";

The above snippet shows the current date and the current date after “2 years 3 months, and 17 days”.

Example 4: Subtracting a Specific INTERVAL From Current DateTime

Similarly, we can subtract a specific INTERVAL from the current date:

SELECT CURRENT_DATE, 
CURRENT_DATE - INTERVAL '2 Years 3 Months 17 Days' AS "Current Date After 2 Years 3 Months 17 Days";

The output shows the current date and the current date before “2 years, 3 months, and 17 days”.

Example 5: How to Format an INTERVAL in Postgres?

To format a specific interval, the TO_CHAR() function is used in Postgres. The return type for the formatted interval will be TEXT:

SELECT TO_CHAR(INTERVAL '2Y 3M 15D 18H 25M 32S', 'YY-MM-DD HH12:MI:SS');

The output snippet verifies that the given interval has been converted into the 12-hours format.

Example 6: How to Extract a Specific Field From the Given INTERVAL in Postgres?

Use the Postgres’ EXTRACT() function to extract a specific field from an INTERVAL in Postgres:

SELECT EXTRACT(HOURS FROM INTERVAL '06H 25M 32S');

The “hours” field has been extracted from the input INTERVAL successfully.

Conclusion

PostgreSQL offers an INTERVAL data type that represents a time duration, such as the time between two events, an event's duration, etc. The “INTERVAL” keyword is used to define an INTERVAL in Postgres. Users can also specify the precision of the INTERVAL data type while defining a table’s column. For instance, a column defined as an interval(4) will store the intervals with up to 4 digits of precision. The precision value is applied to the seconds' field only. This blog explained the usage of the INTERVAL data type in Postgres via suitable examples.