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.

Practice

1. What does the following PostgreSQL expression do?
SELECT CURRENT_DATE + INTERVAL '5 days';

easy

A. Returns the current date without change

B. Subtracts 5 days from the current date

C. Causes a syntax error

D. Adds 5 days to the current date

Solution

1. Step 1: Understand the CURRENT_DATE keyword

   CURRENT_DATE returns today's date without time.

2. Step 2: Add interval of 5 days

   Adding INTERVAL '5 days' increases the date by 5 days.

3. Final Answer:

   Adds 5 days to the current date -> Option D

4. Quick Check:

   Adding interval '5 days' = date + 5 days [OK]

Hint: Adding INTERVAL 'X days' shifts date forward by X days [OK]

Common Mistakes:

• Confusing addition with subtraction
• Thinking CURRENT_DATE includes time
• Using wrong interval syntax

2. Which of the following is the correct syntax to subtract 3 months from a date column named order_date?

easy

A. order_date - '3 months'

B. order_date - INTERVAL '3 months'

C. order_date - INTERVAL 3 months

D. order_date - INTERVAL '3 month'

Solution

1. Step 1: Identify correct interval syntax

   INTERVAL must be followed by a quoted string with number and unit, e.g., '3 months'.

2. Step 2: Check subtraction from date column

   Subtracting INTERVAL '3 months' from order_date is valid syntax.

3. Final Answer:

   order_date - INTERVAL '3 months' -> Option B

4. Quick Check:

   Subtract INTERVAL '3 months' with quotes [OK]

Hint: Always quote interval value like '3 months' [OK]

Common Mistakes:

• Omitting quotes around interval string
• Using singular 'month' instead of plural 'months'
• Trying to subtract string without INTERVAL keyword

3. What is the result of this query if today is 2024-06-15?

SELECT (DATE '2024-06-15' + INTERVAL '1 month' - INTERVAL '10 days')::date AS new_date;

medium

A. 2024-07-25

B. 2024-05-25

C. 2024-07-05

D. 2024-06-05

Solution

1. Step 1: Add 1 month to 2024-06-15

   Adding 1 month results in 2024-07-15.

2. Step 2: Subtract 10 days from 2024-07-15

   Subtracting 10 days gives 2024-07-05.

3. Final Answer:

   2024-07-05 -> Option C

4. Quick Check:

   2024-06-15 + 1 month - 10 days = 2024-07-05 [OK]

Hint: Add then subtract intervals stepwise for correct date [OK]

Common Mistakes:

• Subtracting before adding
• Confusing day and month units
• Not casting to date and including time

4. Identify the error in this query:

SELECT CURRENT_DATE + INTERVAL 5 days;

medium

A. Missing quotes around interval value

B. CURRENT_DATE cannot be used with intervals

C. INTERVAL keyword is misspelled

D. Adding intervals to dates is not allowed

Solution

1. Step 1: Check INTERVAL syntax

   INTERVAL requires the value to be a quoted string, e.g., '5 days'.

2. Step 2: Identify missing quotes error

   The query lacks quotes around 5 days, causing syntax error.

3. Final Answer:

   Missing quotes around interval value -> Option A

4. Quick Check:

   INTERVAL '5 days' needs quotes [OK]

Hint: Always put interval value in single quotes [OK]

Common Mistakes:

• Writing INTERVAL 5 days without quotes
• Assuming CURRENT_DATE can't be used with intervals
• Confusing INTERVAL keyword spelling

5. You want to find all orders from a table orders where the order_date is within the last 3 months from today. Which query correctly uses date arithmetic with intervals?

hard

A. SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '3 months';

B. SELECT * FROM orders WHERE order_date <= CURRENT_DATE + INTERVAL '3 months';

C. SELECT * FROM orders WHERE order_date > CURRENT_DATE + INTERVAL '3 months';

D. SELECT * FROM orders WHERE order_date < CURRENT_DATE - INTERVAL '3 months';

Solution

1. Step 1: Understand the requirement

   We want orders from the last 3 months, so order_date should be after (or equal to) 3 months ago.

2. Step 2: Use CURRENT_DATE minus 3 months

   Subtracting INTERVAL '3 months' from CURRENT_DATE gives the date 3 months ago.

3. Step 3: Write condition for order_date

   order_date >= CURRENT_DATE - INTERVAL '3 months' selects orders within last 3 months.

4. Final Answer:

   SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '3 months'; -> Option A

5. Quick Check:

   Filter dates after CURRENT_DATE - 3 months [OK]

Hint: Use >= CURRENT_DATE - INTERVAL 'X months' for recent dates [OK]

Common Mistakes:

• Using + INTERVAL instead of - INTERVAL
• Using < instead of >= for filtering recent dates
• Confusing direction of date comparison