Date arithmetic with intervals in PostgreSQL - Step-by-Step Execution

Concept Flow - Date arithmetic with intervals

Start with a DATE or TIMESTAMP

↓

Add or Subtract INTERVAL

↓

Calculate new DATE or TIMESTAMP

↓

Return result as new date/time

You start with a date or timestamp, add or subtract an interval, and get a new date or timestamp as the result.

Execution Sample

PostgreSQL

SELECT DATE '2024-06-01' + INTERVAL '3 days' AS new_date;

This query adds 3 days to the date June 1, 2024, returning June 4, 2024.

Execution Table

Step	Expression	Operation	Intermediate Result	Final Result
1	DATE '2024-06-01'	Initial date	2024-06-01	2024-06-01
2	INTERVAL '3 days'	Interval to add	3 days	3 days
3	DATE '2024-06-01' + INTERVAL '3 days'	Add interval to date	2024-06-04	2024-06-04
4	Return result	Output new date	2024-06-04	2024-06-04

💡 Completed addition of interval to date, resulting in new date 2024-06-04

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
date_value	2024-06-01	2024-06-01	2024-06-04	2024-06-04
interval_value	N/A	3 days	3 days	3 days

Key Moments - 2 Insights

Why does adding an interval to a date produce a new date instead of a number?

Because in PostgreSQL, adding an interval to a date shifts the date forward or backward in time, resulting in a new date value, not a numeric calculation. See execution_table row 3 where the date changes from 2024-06-01 to 2024-06-04.

Can intervals include units other than days, like months or hours?

Yes, intervals can include months, days, hours, minutes, and seconds. The interval type is flexible. For example, INTERVAL '1 month 2 days' works similarly. This is implied in step 2 where the interval is defined.

Visual Quiz - 3 Questions

Look at the execution_table, what is the intermediate result after adding the interval to the date?

A 2024-06-04

B 3 days

C 2024-06-01

D N/A

💡 Hint

Check execution_table row 3 under Intermediate Result column.

At which step does the interval get defined in the execution_table?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look for the step where INTERVAL '3 days' is mentioned.

If you subtract INTERVAL '1 month' instead of adding, what would happen to the date_value variable?

A It would increase by one month

B It would decrease by one month

C It would stay the same

D It would become NULL

💡 Hint

Think about how subtraction of intervals affects dates, similar to addition in execution_table row 3.

Concept Snapshot

Date arithmetic with intervals in PostgreSQL:
- Use DATE or TIMESTAMP + INTERVAL to add time
- INTERVAL can be days, months, hours, etc.
- Result is a new date or timestamp
- Subtract intervals similarly
- Example: SELECT DATE '2024-06-01' + INTERVAL '3 days';

Full Transcript

This visual execution shows how PostgreSQL adds an interval to a date. We start with the date 2024-06-01. Then we define an interval of 3 days. Adding this interval to the date shifts it forward by 3 days, resulting in 2024-06-04. The variable tracker shows how the date_value changes from the start to the final result. Key moments clarify why the result is a date, not a number, and that intervals can include various time units. The quiz tests understanding of the steps and effects of adding or subtracting intervals.