DATE_ADD and DATE_SUB in MySQL - Step-by-Step Execution

Concept Flow - DATE_ADD and DATE_SUB

Start with a date

↓

Choose operation: Add or Subtract

↓

DATE_ADD

↓

Add interval

↓

Return new date

↓

End

Start with a date, choose to add or subtract a time interval, then get the new date as result.

Execution Sample

MySQL

SELECT DATE_ADD('2024-06-01', INTERVAL 5 DAY) AS added_date;
SELECT DATE_SUB('2024-06-01', INTERVAL 3 DAY) AS subtracted_date;

Add 5 days and subtract 3 days from the date '2024-06-01'.

Execution Table

Step	Function	Input Date	Interval	Operation	Result
1	DATE_ADD	2024-06-01	5 DAY	Add 5 days	2024-06-06
2	DATE_SUB	2024-06-01	3 DAY	Subtract 3 days	2024-05-29
3	END	-	-	-	Execution complete

💡 All intervals applied, results returned, execution ends.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
Input Date	2024-06-01	2024-06-01	2024-06-01	2024-06-01
Added Date	N/A	2024-06-06	2024-06-06	2024-06-06
Subtracted Date	N/A	N/A	2024-05-29	2024-05-29

Key Moments - 2 Insights

Why does DATE_ADD('2024-06-01', INTERVAL 5 DAY) return '2024-06-06'?

Because it adds 5 days to the original date, moving from June 1 to June 6 as shown in execution_table row 1.

Why does DATE_SUB('2024-06-01', INTERVAL 3 DAY) return '2024-05-29'?

Because it subtracts 3 days from June 1, going back to May 29, as shown in execution_table row 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of DATE_ADD at step 1?

A 2024-06-06

B 2024-06-05

C 2024-05-29

D 2024-06-01

💡 Hint

Check the 'Result' column in execution_table row 1.

At which step does the date become '2024-05-29'?

A Step 1

B Step 2

C Step 3

D Never

💡 Hint

Look at the 'Result' column in execution_table row 2.

If we change the interval in DATE_SUB to 1 DAY, what would be the new result?

A 2024-06-01

B 2024-05-30

C 2024-05-31

D 2024-05-29

💡 Hint

Subtracting 1 day from 2024-06-01 moves the date back by one day.

Concept Snapshot

DATE_ADD(date, INTERVAL n unit) adds time to a date.
DATE_SUB(date, INTERVAL n unit) subtracts time from a date.
Units can be DAY, MONTH, YEAR, etc.
Returns a new date shifted by the interval.
Useful for date calculations in SQL.

Full Transcript

This visual execution shows how DATE_ADD and DATE_SUB work in MySQL. Starting with a date, you choose to add or subtract a time interval. For example, adding 5 days to '2024-06-01' results in '2024-06-06'. Subtracting 3 days from the same date results in '2024-05-29'. The execution table tracks each step and the resulting dates. Variables like input date and results are tracked to show changes. Key moments clarify why the dates change as they do. Quizzes test understanding by asking about specific steps and results. The snapshot summarizes the syntax and behavior for quick reference.