DATE_TRUNC for rounding dates in PostgreSQL - Time & Space Complexity

Time Complexity: DATE_TRUNC for rounding dates

O(n)

Understanding Time Complexity

We want to understand how the time it takes to round dates using DATE_TRUNC changes as we work with more data.

How does the work grow when we apply DATE_TRUNC to many rows?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT DATE_TRUNC('month', order_date) AS month_start,
       COUNT(*) AS orders_count
FROM orders
GROUP BY month_start
ORDER BY month_start;
    

This query rounds each order date down to the first day of its month, then counts orders per month.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Applying DATE_TRUNC to each row's date in the orders table.
• How many times: Once for every row in the orders table.

How Execution Grows With Input

As the number of rows grows, the database must round more dates, so work grows steadily.

Input Size (n)	Approx. Operations
10	10 date rounds
100	100 date rounds
1000	1000 date rounds

Pattern observation: The work grows directly with the number of rows; doubling rows doubles the work.

Final Time Complexity

Time Complexity: O(n)

This means the time to round dates grows in a straight line with the number of rows processed.

Common Mistake

[X] Wrong: "DATE_TRUNC runs once and applies to all rows instantly."

[OK] Correct: Each row's date must be processed separately, so the work adds up with more rows.

Interview Connect

Understanding how functions like DATE_TRUNC scale helps you explain query performance clearly and confidently.

Self-Check

"What if we added an index on the order_date column? How would that affect the time complexity of this query?"