EXTRACT and YEAR/MONTH/DAY in MySQL - Time & Space Complexity

Time Complexity: EXTRACT and YEAR/MONTH/DAY

O(n)

Understanding Time Complexity

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

How does extracting year, month, or day from many rows affect performance?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

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

This query extracts the year, month, and day 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 date parts from each row's order_date.
• How many times: Once for every row in the orders table.

How Execution Grows With Input

Each row requires extracting year, month, and day once, so the work grows directly with the number of rows.

Input Size (n)	Approx. Operations
10	30 (3 parts x 10 rows)
100	300 (3 parts x 100 rows)
1000	3000 (3 parts x 1000 rows)

Pattern observation: The total work increases steadily and directly as the number of rows increases.

Final Time Complexity

Time Complexity: O(n)

This means the time to extract date parts grows in a straight line with the number of rows.

Common Mistake

[X] Wrong: "Extracting year, month, and day is a heavy operation that grows faster than the number of rows."

[OK] Correct: Extracting parts from a date is a simple, fixed-cost operation done once per row, so it grows linearly, not faster.

Interview Connect

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

Self-Check

"What if we added a JOIN to another table before extracting date parts? How would the time complexity change?"

Practice

1. What does the MySQL function EXTRACT(YEAR FROM '2023-07-15') return?

easy

A. 07

B. '2023-07-15'

C. 15

D. 2023

Solution

1. Step 1: Understand EXTRACT function usage

   The EXTRACT function retrieves a specific part of a date, here the YEAR part.

2. Step 2: Apply EXTRACT to the given date

   Extracting YEAR from '2023-07-15' returns 2023 as a number.

3. Final Answer:

   2023 -> Option D

4. Quick Check:

   EXTRACT(YEAR FROM date) = year number [OK]

Hint: EXTRACT(YEAR FROM date) gives the year number [OK]

Common Mistakes:

• Confusing YEAR with MONTH or DAY
• Expecting a string instead of a number
• Using EXTRACT without FROM keyword

2. Which of the following is the correct syntax to extract the month from a date column named order_date in MySQL?

easy

A. EXTRACT(MONTH FROM order_date)

B. EXTRACT(order_date FROM MONTH)

C. EXTRACT(MONTH order_date)

D. EXTRACT(MONTH, order_date)

Solution

1. Step 1: Recall EXTRACT syntax

   The correct syntax is EXTRACT(part FROM date), where part is YEAR, MONTH, or DAY.

2. Step 2: Match syntax to options

   EXTRACT(MONTH FROM order_date) uses EXTRACT(MONTH FROM order_date), which is correct syntax.

3. Final Answer:

   EXTRACT(MONTH FROM order_date) -> Option A

4. Quick Check:

   EXTRACT(part FROM date) syntax is correct [OK]

Hint: Use EXTRACT(part FROM date) syntax exactly [OK]

Common Mistakes:

• Omitting FROM keyword
• Swapping arguments order
• Using commas instead of FROM

3. Given the table events with a column event_date storing dates, what will this query return?

SELECT EXTRACT(DAY FROM event_date) AS day FROM events WHERE EXTRACT(MONTH FROM event_date) = 12;

medium

A. Syntax error due to wrong EXTRACT usage

B. All days of events that happened in December

C. All years of events that happened in December

D. All months of events that happened in December

Solution

1. Step 1: Understand the WHERE clause

   The WHERE clause filters rows where the month part of event_date is 12 (December).

2. Step 2: Understand the SELECT clause

   The SELECT clause extracts the day part from event_date for those filtered rows.

3. Final Answer:

   All days of events that happened in December -> Option B

4. Quick Check:

   EXTRACT(MONTH) filters December, EXTRACT(DAY) selects day [OK]

Hint: Filter by EXTRACT(MONTH), select EXTRACT(DAY) for days [OK]

Common Mistakes:

• Confusing day and month extraction
• Thinking EXTRACT returns strings
• Assuming syntax error without checking carefully

4. Identify the error in this query:

SELECT EXTRACT(YEAR order_date) FROM orders;

medium

A. EXTRACT cannot be used with YEAR

B. order_date should be in quotes

C. Missing FROM keyword after YEAR

D. No error, query is correct

Solution

1. Step 1: Check EXTRACT syntax

   EXTRACT requires the format EXTRACT(part FROM date), so FROM keyword is mandatory.

2. Step 2: Identify missing FROM keyword

   The query misses FROM between YEAR and order_date, causing syntax error.

3. Final Answer:

   Missing FROM keyword after YEAR -> Option C

4. Quick Check:

   EXTRACT(part FROM date) needs FROM [OK]

Hint: Always include FROM between part and date in EXTRACT [OK]

Common Mistakes:

• Omitting FROM keyword
• Putting column name in quotes
• Thinking EXTRACT can't use YEAR

5. You want to count how many orders were placed each year and month from the orders table with a created_at datetime column. Which query correctly groups by year and month using EXTRACT?

hard

A. SELECT EXTRACT(YEAR FROM created_at) AS year, EXTRACT(MONTH FROM created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

B. SELECT YEAR(created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

C. SELECT EXTRACT(YEAR, created_at) AS year, EXTRACT(MONTH, created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

D. SELECT EXTRACT(YEAR FROM created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

Solution

1. Step 1: Understand grouping by year and month

   We need to extract year and month parts from created_at to group orders by these parts.

2. Step 2: Check EXTRACT syntax correctness

   SELECT EXTRACT(YEAR FROM created_at) AS year, EXTRACT(MONTH FROM created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; uses correct EXTRACT syntax for both year and month and groups by aliases year and month.

3. Step 3: Verify other options

   SELECT YEAR(created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; uses YEAR() and MONTH() functions which also work but question asks for EXTRACT usage. SELECT EXTRACT(YEAR, created_at) AS year, EXTRACT(MONTH, created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; has wrong EXTRACT syntax. SELECT EXTRACT(YEAR FROM created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; mixes EXTRACT and MONTH() inconsistently.

4. Final Answer:

   Option A query with correct EXTRACT syntax and grouping -> Option A

5. Quick Check:

   Use EXTRACT(part FROM date) consistently for grouping [OK]

Hint: Use EXTRACT(part FROM date) for both year and month [OK]

Common Mistakes:

• Using wrong EXTRACT syntax with commas
• Mixing EXTRACT and other date functions inconsistently
• Not grouping by extracted aliases