Date arithmetic with intervals in PostgreSQL - Time & Space Complexity

Time Complexity: Date arithmetic with intervals

O(n)

Understanding Time Complexity

We want to understand how the time it takes to do date calculations changes as we work with more data.

Specifically, how adding or subtracting intervals from dates behaves when done many times.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

WITH dates AS (
  SELECT generate_series(1, 1000) AS day_num
)
SELECT day_num, 
       (CURRENT_DATE + (day_num || ' days')::interval) AS new_date
FROM dates;
    

This code generates 1000 numbers and adds that many days as intervals to the current date.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Adding an interval to a date for each number generated.
• How many times: Once for each row generated, here 1000 times.

How Execution Grows With Input

Each additional row means one more date plus interval calculation.

Input Size (n)	Approx. Operations
10	10 date + interval additions
100	100 date + interval additions
1000	1000 date + interval additions

Pattern observation: The work grows directly with the number of rows; double the rows, double the calculations.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete grows in a straight line as the number of date calculations increases.

Common Mistake

[X] Wrong: "Adding intervals to dates happens instantly no matter how many rows there are."

[OK] Correct: Each addition is a separate calculation, so more rows mean more work and more time.

Interview Connect

Understanding how operations scale with data size helps you explain and predict query performance clearly and confidently.

Self-Check

"What if we changed the interval from days to months? How would the time complexity change?"

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