What Is An Interval In PostgreSQL?

In PostgreSQL, an interval is a data type that represents a span of time. It can be used to store differences between dates or times, such as durations or intervals between two timestamps. Intervals are especially useful when you need to perform date or time arithmetic, such as adding or subtracting time from a date, or calculating the difference between two timestamps.

Key Features of the Interval Data Type:

• Syntax: Intervals are expressed in terms of years, months, days, hours, minutes, and seconds.
• Granularity: Intervals can represent time at different granularities (e.g., days, months, or seconds).

Examples:

1. Creating an Interval:
   • INTERVAL '5 days'
   • INTERVAL '2 hours 30 minutes'
   • INTERVAL '3 months 2 days'
   • INTERVAL '1 year 3 months'
2. Interval Arithmetic:
   • Add an interval to a date or timestamp:

     SELECT '2025-01-01'::DATE + INTERVAL '1 year';
     

   • Subtract two dates or timestamps to get an interval:

     SELECT '2025-01-01'::DATE - '2024-01-01'::DATE;
     

3. Interval Operations:
   • You can add or subtract intervals from DATE, TIME, TIMESTAMP, and TIMESTAMPTZ data types.

Example Usage:

SELECT current_date + INTERVAL '1 month'; -- Adds one month to the current date
SELECT current_timestamp - INTERVAL '5 days'; -- Subtracts 5 days from the current timestamp

Intervals are flexible and can handle complex time calculations, such as handling leap years, time zones, and different month lengths, making them essential for working with time-based data.