Working with Time Intervals: INTERVAL

A workout lasts an hour and a half, a workday is 8 hours, a break is 20 minutes, and boiling eggs takes 10 minutes. All of this is about time intervals.

Time intervals are super useful in real apps when you need to, for example, calculate task deadlines, figure out the difference between two dates, or add/subtract time. But before we dive into the details, let’s quickly recap where we left off in the previous lectures.

In PostgreSQL, INTERVAL is a special data type made for representing spans of time. Unlike other time types, it doesn’t store a specific date or time, but describes a period, like: "2 days", "3 hours", "5 minutes".

INTERVAL Syntax

You can set intervals in this format:

INTERVAL 'number time_unit'

Examples:

INTERVAL '2 days' -- Two days
INTERVAL '3 hours' -- Three hours
INTERVAL '15 minutes' -- Fifteen minutes
INTERVAL '1 day 2 hours' -- One day and two hours

PostgreSQL supports different time units: year, month, day, hour, minute, second. You can mix and match them in one expression.

Using Intervals in Queries

The INTERVAL data type gets especially handy when you start using it together with other time types (DATE, TIMESTAMP). Let’s check out the main operations.

Adding an interval to a date/time

You can add intervals to date or time values. For example:

SELECT CURRENT_DATE + INTERVAL '7 days' AS delivery_date;
-- Get the date 7 days from today

Result:

Subtracting intervals from date/time

Besides adding, you can also subtract intervals:

SELECT NOW() - INTERVAL '2 hours' AS two_hours_ago;
-- Get the time two hours ago

Result:

Calculating the difference between two dates

One of the cool features of INTERVAL is calculating the difference between two dates:

SELECT '2023-10-15'::DATE - '2023-10-01'::DATE AS days_difference;
-- How many days between two dates

Result:

Notice that the result here is also shown as a time interval.

Practical Examples of Using INTERVAL

Calculating delivery dates. Let’s say you have an online store where delivery takes from 3 to 7 days. Here’s how you can calculate possible delivery dates:

SELECT CURRENT_DATE + INTERVAL '3 days' AS earliest_delivery,
       CURRENT_DATE + INTERVAL '7 days' AS latest_delivery;

Result:

Tracking task completion time. Suppose you have a tasks table, where each task has a start date. You need to calculate the end date of a task based on its duration in hours:

CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    task_name TEXT,
    start_time TIMESTAMP,
    duration INTERVAL
);

INSERT INTO tasks (task_name, start_time, duration)
VALUES
('Prepare report', '2023-10-01 09:00:00', INTERVAL '4 hours'),
('Test application', '2023-10-01 10:00:00', INTERVAL '2 hours 30 minutes');

SELECT task_name,
       start_time,
       start_time + duration AS end_time
FROM tasks;

Result:

Comparing task durations. Sometimes you want to find all tasks that took less than 3 hours:

SELECT task_name
FROM tasks
WHERE duration < INTERVAL '3 hours';

Result:

Handy INTERVAL Tricks

Dynamically calculating intervals. You can combine intervals with other operations. For example, take the number of days from a column and turn it into an interval:

SELECT CURRENT_DATE + (order_days || ' days')::INTERVAL AS order_due_date
FROM orders;

Casting intervals to string. Sometimes you need to cast an interval to a string for display:

SELECT INTERVAL '2 days 3 hours'::TEXT AS interval_text;

Result:

Common Mistakes When Using INTERVAL

Working with INTERVAL, even though it’s powerful, can sometimes be tricky. One typical mistake is using the wrong format. For example, if you try INTERVAL '2 hours and 30 minutes', you’ll get an error, because PostgreSQL doesn’t get the word "and". The right way: INTERVAL '2 hours 30 minutes'.

Another mistake is forgetting to specify the time unit, like in INTERVAL '2'. PostgreSQL doesn’t know what this "2" is, so always make sure to clearly say the unit (2 days, 2 hours).