PostgreSQL Interval Data Type (original) (raw)

Last Updated : 15 Jul, 2025

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.