Interval type for durations in PostgreSQL - Step-by-Step Execution

Concept Flow - Interval type for durations

Start: Define Interval

↓

Use INTERVAL keyword

↓

Specify duration (e.g., '2 days')

↓

Store or use interval value

↓

Perform operations (add, subtract, compare)

↓

Get result as duration

The flow shows how to define and use interval values for durations in PostgreSQL, from specifying to using them in calculations.

Execution Sample

PostgreSQL

SELECT INTERVAL '2 days' AS duration;
SELECT NOW() + INTERVAL '1 hour' AS future_time;
SELECT INTERVAL '1 day 2 hours' - INTERVAL '3 hours' AS diff;

These queries create interval durations, add intervals to timestamps, and subtract intervals to find differences.

Execution Table

Step	Query	Action	Result
1	SELECT INTERVAL '2 days' AS duration;	Create interval of 2 days	duration = 2 days
2	SELECT NOW() + INTERVAL '1 hour' AS future_time;	Add 1 hour interval to current timestamp	future_time = current time + 1 hour
3	SELECT INTERVAL '1 day 2 hours' - INTERVAL '3 hours' AS diff;	Subtract 3 hours from 1 day 2 hours	diff = 23 hours
4	END	No more queries	Execution complete

💡 All interval operations executed and returned expected duration results.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
duration	NULL	2 days	2 days	2 days	2 days
future_time	NULL	NULL	current time + 1 hour	current time + 1 hour	current time + 1 hour
diff	NULL	NULL	NULL	23 hours	23 hours

Key Moments - 2 Insights

Why does subtracting '3 hours' from '1 day 2 hours' result in '23 hours' instead of '21 hours'?

Because '1 day 2 hours' equals 26 hours total. Subtracting 3 hours leaves 23 hours, as shown in execution_table row 3.

Can intervals be added directly to timestamps?

Yes, as in execution_table row 2, adding INTERVAL '1 hour' to NOW() returns a timestamp 1 hour in the future.

Visual Quiz - 3 Questions

Look at the execution_table at step 1, what is the value of 'duration'?

A 2 days

B 1 hour

C 23 hours

D NULL

💡 Hint

Check the 'Result' column in execution_table row 1.

At which step does 'future_time' get assigned a value?

A Step 1

B Step 2

C Step 3

D Never

💡 Hint

Look at variable_tracker row for 'future_time' and see when it changes from NULL.

If you subtract INTERVAL '2 hours' instead of '3 hours' in step 3, what would 'diff' be?

A 23 hours

B 22 hours

C 24 hours

D 21 hours

💡 Hint

Refer to execution_table row 3 and adjust the subtraction accordingly.

Concept Snapshot

INTERVAL type stores durations like '2 days' or '1 hour'.
Use INTERVAL keyword with quoted strings.
Add intervals to timestamps for date/time math.
Subtract intervals to find duration differences.
Intervals can combine days, hours, minutes, seconds.

Full Transcript

This lesson shows how PostgreSQL uses the INTERVAL type to represent durations. You write INTERVAL followed by a quoted string like '2 days' to create a duration. You can add intervals to timestamps, for example adding '1 hour' to the current time. You can also subtract intervals from each other to find differences, like subtracting '3 hours' from '1 day 2 hours' resulting in '23 hours'. The execution table traces these steps, showing how variables change and what results are returned. Key points include understanding that intervals represent total time durations and can be used in arithmetic with timestamps and other intervals.