Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-interval-data-type/ below:

PostgreSQL - Interval Data Type

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.

interval [ Data_fields ] [ (p) ]

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

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.

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

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: 

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: 

• 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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4