DATEDIFF and TIMESTAMPDIFF in MySQL - Step-by-Step Execution

Concept Flow - DATEDIFF and TIMESTAMPDIFF

Start with two dates

↓

Choose function: DATEDIFF or TIMESTAMPDIFF

↓

DATEDIFF: Calculate days between dates

↓

TIMESTAMPDIFF: Choose unit (e.g., SECOND, MINUTE, DAY)

↓

Calculate difference in chosen unit

↓

Return integer result

DATEDIFF returns the number of days between two dates. TIMESTAMPDIFF returns the difference between two dates or datetimes in a specified unit.

Execution Sample

MySQL

SELECT DATEDIFF('2024-06-10', '2024-06-01') AS diff_days;
SELECT TIMESTAMPDIFF(HOUR, '2024-06-01 10:00:00', '2024-06-02 15:00:00') AS diff_hours;

Calculate days difference with DATEDIFF and hours difference with TIMESTAMPDIFF between two date/datetime values.

Execution Table

Step	Function	Input 1	Input 2	Unit (for TIMESTAMPDIFF)	Calculation	Result
1	DATEDIFF	'2024-06-10'	'2024-06-01'	N/A	2024-06-10 minus 2024-06-01 in days	9
2	TIMESTAMPDIFF	'2024-06-01 10:00:00'	'2024-06-02 15:00:00'	HOUR	Difference in hours between the two timestamps	29
3	TIMESTAMPDIFF	'2024-06-01 10:00:00'	'2024-06-02 15:00:00'	DAY	Difference in days between the two timestamps	1
4	DATEDIFF	'2024-06-01'	'2024-06-10'	N/A	2024-06-01 minus 2024-06-10 in days	-9
5	TIMESTAMPDIFF	'2024-06-02 15:00:00'	'2024-06-01 10:00:00'	HOUR	Difference in hours reversed	-29
6	END				No more calculations

💡 All differences calculated; negative results show reversed date order.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
diff_days	N/A	9	9	9	-9	-9	-9
diff_hours	N/A	N/A	29	29	29	-29	-29
diff_days_timestamp	N/A	N/A	N/A	1	1	1	1

Key Moments - 3 Insights

Why does DATEDIFF return a negative number in step 4?

DATEDIFF subtracts the second date from the first. If the first date is earlier, the result is negative, as shown in step 4 where '2024-06-01' minus '2024-06-10' equals -9.

Why do TIMESTAMPDIFF results depend on the unit chosen?

TIMESTAMPDIFF calculates difference in the unit you specify (e.g., HOUR, DAY). Step 2 shows 29 hours difference, while step 3 shows 1 day difference for the same timestamps.

Can DATEDIFF be used with time values?

No, DATEDIFF only works with dates and returns difference in days. For time or smaller units, TIMESTAMPDIFF should be used, as shown in steps 2 and 3.

Visual Quiz - 3 Questions

Look at the execution table, what is the result of DATEDIFF('2024-06-10', '2024-06-01') at step 1?

A -9

B 1

C 9

D 29

💡 Hint

Check the 'Result' column in row for step 1 in the execution_table.

At which step does TIMESTAMPDIFF calculate the difference in days?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Look at the 'Unit' column in the execution_table to find where 'DAY' is used.

If the dates in step 5 were swapped, what would be the new result for TIMESTAMPDIFF in hours?

A 1

B 29

C -29

D -1

💡 Hint

Refer to step 2 and 5 in execution_table to see how swapping inputs changes sign.

Concept Snapshot

DATEDIFF(date1, date2) returns days between dates (date1 - date2).
TIMESTAMPDIFF(unit, datetime1, datetime2) returns difference in specified unit.
Units include SECOND, MINUTE, HOUR, DAY, MONTH, YEAR.
Negative results mean the first date/time is earlier.
Use DATEDIFF for days only; TIMESTAMPDIFF for flexible units.

Full Transcript

This visual execution shows how MySQL functions DATEDIFF and TIMESTAMPDIFF calculate differences between dates and datetimes. DATEDIFF returns the number of days between two dates by subtracting the second date from the first. TIMESTAMPDIFF allows specifying the unit of difference, such as hours or days, and calculates accordingly. Negative results occur when the first date/time is earlier than the second. The execution table traces step-by-step calculations with example inputs, showing results and how changing units or input order affects output. Variable tracking highlights how difference values change after each step. Key moments clarify common confusions about negative results, unit choices, and function limitations. The quiz tests understanding by referencing specific steps and results. The snapshot summarizes syntax and behavior for quick reference.