# PostgreSQL INTERVAL Data Type With Examples

> The INTERVAL keyword is used to define an interval in Postgres. This blog explained the usage of the INTERVAL data type in Postgres via suitable examples.

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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-interval-data-type-with-examples/)