DATE_FORMAT and EXTRACT in SQL - Time & Space Complexity

Time Complexity: DATE_FORMAT and EXTRACT

O(n)

Understanding Time Complexity

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

How does using DATE_FORMAT or EXTRACT on many rows affect the work done?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT
  DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date,
  EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;

This query formats the date and extracts the year from each order's date in the orders table.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

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

How Execution Grows With Input

As the number of rows grows, the work grows in the same way because each row needs its date processed.

Input Size (n)	Approx. Operations
10	20 date operations
100	200 date operations
1000	2000 date operations

Pattern observation: The number of operations grows directly with the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows in a straight line as the number of rows increases.

Common Mistake

[X] Wrong: "Using DATE_FORMAT or EXTRACT is slow because they do complex calculations that multiply with data size."

[OK] Correct: These functions run once per row and are simple operations, so the time grows linearly, not exponentially.

Interview Connect

Understanding how simple functions like DATE_FORMAT and EXTRACT scale helps you explain query performance clearly and confidently.

Self-Check

What if we added a JOIN that doubles the number of rows? How would the time complexity change?