What is Interval in PostgreSQL: Explanation and Examples
interval is a data type used to represent a span of time, such as days, hours, or minutes. It allows you to store and perform calculations with durations rather than fixed points in time.How It Works
Think of interval as a way to measure the length of time between two moments, like measuring how long a movie lasts or how many days until your birthday. Instead of storing a specific date or time, it stores a duration, such as '3 days' or '2 hours 30 minutes'.
PostgreSQL lets you add or subtract these intervals from dates or timestamps to calculate new dates or durations. For example, you can add an interval of '1 month' to today's date to find the date one month from now. Internally, intervals can include multiple parts like years, months, days, hours, minutes, and seconds.
Example
SELECT CURRENT_DATE AS today, CURRENT_DATE + INTERVAL '10 days' AS ten_days_later;
When to Use
Use interval when you need to work with durations or time spans rather than fixed dates. For example, you can calculate how long a project took, schedule events a certain time after another, or find the difference between two timestamps.
Common real-world uses include calculating subscription periods, setting reminders, or measuring elapsed time in logs.
Key Points
- Interval stores a length of time, not a specific date or time.
- You can add or subtract intervals from dates or timestamps.
- Intervals can include years, months, days, hours, minutes, and seconds.
- Useful for scheduling, duration calculations, and time arithmetic.