Understanding how to represent and manipulate durations or time differences is crucial in many real-world applications, such as tracking elapsed time or calculating deadlines. In SQL, the INTERVAL type is designed specifically for this purpose. An INTERVAL value expresses a span of time, such as "2 days", "3 hours", or even more complex durations like "1 year 2 months 10 days 5 hours 30 minutes". This flexibility allows you to store and compute with durations directly in your database tables.

-- Creating a table with an INTERVAL column for storing durations
CREATE TABLE project_tasks (
    task_id SERIAL PRIMARY KEY,
    task_name VARCHAR(100),
    estimated_duration INTERVAL
);

By adding an INTERVAL column to your table, as in the project_tasks example, you can assign each task a specific duration. The INTERVAL type can represent time spans as small as microseconds or as large as years, depending on your needs. This makes it highly versatile for everything from scheduling events to measuring timeouts.

You can use INTERVAL values in calculations, such as adding a duration to a date or timestamp, or computing the difference between two timestamps. For instance, if you want to know when a task is expected to finish, you can add its estimated_duration to the task's start date. Similarly, you can subtract two timestamps to find out how much time has elapsed, and the result will be an INTERVAL.

-- Inserting INTERVAL values into the table
INSERT INTO project_tasks (task_name, estimated_duration)
VALUES
    ('Design phase', INTERVAL '2 weeks'),
    ('Development', INTERVAL '1 month 10 days'),
    ('Testing', INTERVAL '5 days 4 hours');

-- Using INTERVAL in a calculation: adding estimated_duration to a start date
SELECT
    task_name,
    DATE '2024-07-01' AS start_date,
    DATE '2024-07-01' + estimated_duration AS expected_end_date
FROM project_tasks;

-- Subtracting timestamps to get an INTERVAL
SELECT
    special_timestamp,
    special_timestamp - INTERVAL '1 day' AS one_day_earlier
FROM dates_specials;