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?"