YEAR, MONTH, DAY extraction in SQL - Time & Space Complexity

Time Complexity: YEAR, MONTH, DAY extraction

O(n)

Understanding Time Complexity

We want to understand how the time to extract parts of a date grows as we have more data.

How does the work change when we extract year, month, or day from many rows?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT
  YEAR(order_date) AS order_year,
  MONTH(order_date) AS order_month,
  DAY(order_date) AS order_day
FROM orders;

This query extracts the year, month, and day parts from the order_date column for every row in the orders table.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Extracting year, month, and day from each date value.
• How many times: Once for each row in the orders table.

How Execution Grows With Input

Each row requires the same fixed amount of work to extract the date parts.

Input Size (n)	Approx. Operations
10	10 extractions
100	100 extractions
1000	1000 extractions

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

Final Time Complexity

Time Complexity: O(n)

This means the time to extract year, month, and day grows linearly with the number of rows.

Common Mistake

[X] Wrong: "Extracting year, month, and day is a constant time operation regardless of rows, so the query runs instantly no matter the data size."

[OK] Correct: While each extraction is quick, the database must do it for every row, so more rows mean more total work and longer time.

Interview Connect

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

Self-Check

"What if we added a WHERE clause to filter rows before extracting year, month, and day? How would the time complexity change?"