PostgreSQL – Interval Data Type

Last Updated : 25 Aug, 2024

The interval data type in PostgreSQL stores time periods using 16 bytes of storage and supports a range from -178,000,000 years to 178,000,000 years. It provides a precision attribute (‘p’) that allows you to specify the number of fractional digits retained in the seconds field, enhancing the precision of time calculations and results.

Let us get a better understanding of the Interval Data Type in PostgreSQL from this article.

Syntax

interval [ Data_fields ] [ (p) ]

Parameters:

fields: Specifies which parts of the interval to store (e.g., YEAR, MONTH, DAY, HOUR, MINUTE, SECOND).
p: Precision, defines the fractional seconds precision.

Storage Details

PostgreSQL internally stores interval values as a combination of months, days, and seconds, where:

Months and days are stored as integers.
Seconds can include fractions, based on the specified precision.

PostgreSQL Interval Data Type Examples

Now let’s jump into a few examples for demonstration.

Example 1: Calculating Time in the Past

In this example we will query to know the time of 4 hours 25 minutes ago at the current time of last year using the below commands.

Query:

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

Output:

PostgreSQL Interval Data Type Example

Example 2: Formatting Interval Values

In this example, we will convert an interval value ta string format using the ‘TO_CHAR()’ function. The ‘TO_CHAR()’ function takes the first argument as an interval value, the second one as the format, and returns a string that represents the interval in the specified format.

Query:

SELECT 
    TO_CHAR(
        INTERVAL '15h 25m 12s',
        'HH24:MI:SS'
    );

Output:

PostgreSQL Interval Data Type Example

Important Points About PostgreSQL Interval Data Type

Users can specify the fractional seconds precision with the precision attribute (‘p’). This allows for precise control over how much detail about seconds is stored.

Combine ‘interval’ with other PostgreSQL functions like ‘date_trunc‘ to simplify complex time-based queries and enhance data granularity.

PostgreSQL automatically normalizes interval values when they are stored. For example, if you input an interval of ‘25 hours‘, PostgreSQL will store it as ‘1 day and 1 hour’.

The interval type supports mixed units, like combining years, months, days, and times together.