DATE_ADD and DATE_SUB in MySQL - Time & Space Complexity

Time Complexity: DATE_ADD and DATE_SUB

O(n)

Understanding Time Complexity

When using DATE_ADD and DATE_SUB in MySQL, it's helpful to know how the time to run these functions changes as the amount of data grows.

We want to see how the work done grows when we apply these date functions to many rows.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT order_id, order_date, 
       DATE_ADD(order_date, INTERVAL 7 DAY) AS delivery_date
FROM orders;
    

This query adds 7 days to the order_date for each order to find the delivery date.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

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

How Execution Grows With Input

Each row requires one date addition operation, so the total work grows directly with the number of rows.

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

Pattern observation: The work increases in a straight line as the number of rows increases.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows directly in proportion to the number of rows processed.

Common Mistake

[X] Wrong: "DATE_ADD runs in constant time no matter how many rows there are."

[OK] Correct: The function runs once per row, so more rows mean more total work.

Interview Connect

Understanding how simple functions like DATE_ADD scale with data size 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?"

Practice

1. What does the MySQL function DATE_ADD(date, INTERVAL expr unit) do?

easy

A. Returns the current date and time

B. Subtracts a specified time interval from a date

C. Adds a specified time interval to a date

D. Formats a date into a string

Solution

1. Step 1: Understand the function purpose

   The function DATE_ADD is designed to add a time interval to a given date value.

2. Step 2: Compare options with function behavior

   'Adds a specified time interval to a date' correctly states that it adds a specified time interval. 'Subtracts a specified time interval from a date' describes DATE_SUB, not DATE_ADD. The options for returning the current date and time and formatting a date into a string describe unrelated functions.

3. Final Answer:

   Adds a specified time interval to a date -> Option C

4. Quick Check:

   DATE_ADD adds time interval [OK]

Hint: DATE_ADD adds time; DATE_SUB subtracts time [OK]

Common Mistakes:

• Confusing DATE_ADD with DATE_SUB
• Thinking DATE_ADD formats dates
• Assuming DATE_ADD returns current date

2. Which of the following is the correct syntax using DATE_SUB to subtract 10 days from the date '2024-06-15' in MySQL?

easy

A. DATE_ADD('2024-06-15', INTERVAL -10 DAY)

B. DATE_SUB('2024-06-15', INTERVAL 10 DAY)

C. DATE_SUB('2024-06-15', 10 DAY)

D. DATE_ADD('2024-06-15', 10 DAYS)

Solution

1. Step 1: Check correct function and syntax for subtraction

   To subtract days, use DATE_SUB with INTERVAL keyword and unit DAY.

2. Step 2: Validate each option's syntax

   DATE_SUB('2024-06-15', INTERVAL 10 DAY) uses correct syntax: DATE_SUB('date', INTERVAL 10 DAY). DATE_ADD('2024-06-15', INTERVAL -10 DAY) uses DATE_ADD with negative interval, which is not recommended. DATE_SUB('2024-06-15', 10 DAY) misses INTERVAL keyword. DATE_ADD('2024-06-15', 10 DAYS) misses the INTERVAL keyword and uses DATE_ADD which adds instead of subtracts.

3. Final Answer:

   DATE_SUB('2024-06-15', INTERVAL 10 DAY) -> Option B

4. Quick Check:

   Correct syntax uses DATE_SUB with INTERVAL [OK]

Hint: Use DATE_SUB with INTERVAL and unit DAY to subtract days [OK]

Common Mistakes:

• Omitting INTERVAL keyword
• Using plural unit like DAYS instead of DAY
• Using DATE_ADD with negative interval

3. What is the result of this query?

SELECT DATE_ADD('2024-06-10', INTERVAL 3 MONTH) AS new_date;

medium

A. 2024-03-10

B. 2024-06-10

C. 2024-06-13

D. 2024-09-10

Solution

1. Step 1: Understand DATE_ADD with MONTH interval

   The query adds 3 months to the date '2024-06-10'. Adding 3 months to June 10, 2024, results in September 10, 2024.

2. Step 2: Match the calculated date with options

   2024-09-10 shows '2024-09-10', which matches the expected result. Other options are incorrect dates or no change.

3. Final Answer:

   2024-09-10 -> Option D

4. Quick Check:

   June 10 + 3 months = September 10 [OK]

Hint: Add months carefully; day stays same if possible [OK]

Common Mistakes:

• Adding days instead of months
• Confusing month addition with subtraction
• Expecting day to change unexpectedly

4. Identify the error in this MySQL query:

SELECT DATE_SUB('2024-06-20', 5 DAY);

medium

A. Missing INTERVAL keyword before 5 DAY

B. Incorrect function name, should be DATE_ADD

C. Date format is invalid

D. DAY should be plural DAYS

Solution

1. Step 1: Review DATE_SUB syntax

   The correct syntax requires the INTERVAL keyword before the number and unit, like DATE_SUB(date, INTERVAL expr unit).

2. Step 2: Identify the missing keyword

   The query uses DATE_SUB('2024-06-20', 5 DAY) missing INTERVAL, causing syntax error.

3. Final Answer:

   Missing INTERVAL keyword before 5 DAY -> Option A

4. Quick Check:

   DATE_SUB needs INTERVAL keyword [OK]

Hint: Always include INTERVAL keyword with DATE_ADD/SUB [OK]

Common Mistakes:

• Omitting INTERVAL keyword
• Using plural unit DAYS instead of DAY
• Confusing DATE_ADD and DATE_SUB

5. You want to find the date 2 weeks before the last day of the current month in MySQL using DATE_SUB. Which query correctly achieves this?

hard

A. SELECT DATE_SUB(LAST_DAY(CURDATE()), INTERVAL 2 WEEK) AS result;

B. SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL -2 WEEK) AS result;

C. SELECT DATE_SUB(CURDATE(), INTERVAL 2 WEEK) AS result;

D. SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 2 WEEK) AS result;

Solution

1. Step 1: Understand the requirement

   We need the date 2 weeks before the last day of the current month. LAST_DAY(CURDATE()) gives the last day of this month.

2. Step 2: Apply correct date arithmetic

   Subtracting 2 weeks from that date is done with DATE_SUB(..., INTERVAL 2 WEEK). SELECT DATE_SUB(LAST_DAY(CURDATE()), INTERVAL 2 WEEK) AS result; uses this correctly. SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL -2 WEEK) AS result; uses DATE_ADD instead of DATE_SUB. SELECT DATE_SUB(CURDATE(), INTERVAL 2 WEEK) AS result; subtracts from current date, not last day. SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 2 WEEK) AS result; adds 2 weeks, which is incorrect.

3. Final Answer:

   SELECT DATE_SUB(LAST_DAY(CURDATE()), INTERVAL 2 WEEK) AS result; -> Option A

4. Quick Check:

   Subtract 2 weeks from last day using DATE_SUB [OK]

Hint: Use LAST_DAY with DATE_SUB for relative month-end dates [OK]

Common Mistakes:

• Subtracting from current date instead of last day
• Using DATE_ADD with positive interval instead of subtraction
• Confusing weeks with days in interval