DATEDIFF and TIMESTAMPDIFF in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate days difference using DATEDIFF

What is the output of this query?

SELECT DATEDIFF('2024-06-15', '2024-06-10') AS days_diff;

MySQL

SELECT DATEDIFF('2024-06-15', '2024-06-10') AS days_diff;

A 5

B 6

C -5

D 10

💡 Hint

DATEDIFF returns the number of days between two dates as end_date - start_date.

DATEDIFF counts how many days are between the first and second date. Here, 2024-06-15 minus 2024-06-10 equals 5 days.

❓ query_result

intermediate

Calculate months difference using TIMESTAMPDIFF

What is the output of this query?

SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', '2024-06-01') AS months_diff;

MySQL

SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', '2024-06-01') AS months_diff;

A 19

B 18

C 17

D 16

💡 Hint

TIMESTAMPDIFF counts full months between the two dates.

From January 1, 2023 to June 1, 2024 is 17 full months.

📝 Syntax

advanced

Identify the syntax error in TIMESTAMPDIFF usage

Which option contains a syntax error when using TIMESTAMPDIFF in MySQL?

A SELECT TIMESTAMPDIFF('DAY', '2024-01-01', '2024-01-10');

B SELECT TIMESTAMPDIFF(MONTH, '2024-01-01', '2024-02-01');

C SELECT TIMESTAMPDIFF(DAY, '2024-01-01', '2024-01-10');

D SELECT TIMESTAMPDIFF(YEAR, '2023-01-01', '2024-01-01');

💡 Hint

The first argument to TIMESTAMPDIFF should be a keyword, not a string.

TIMESTAMPDIFF expects the unit (e.g., DAY, MONTH) without quotes. Option A uses quotes, causing a syntax error.

❓ query_result

advanced

Compare DATEDIFF and TIMESTAMPDIFF outputs

What is the output of this query?

SELECT DATEDIFF('2024-06-15 12:00:00', '2024-06-10 23:59:59') AS diff1, TIMESTAMPDIFF(DAY, '2024-06-10 23:59:59', '2024-06-15 12:00:00') AS diff2;

MySQL

SELECT DATEDIFF('2024-06-15 12:00:00', '2024-06-10 23:59:59') AS diff1, TIMESTAMPDIFF(DAY, '2024-06-10 23:59:59', '2024-06-15 12:00:00') AS diff2;

A diff1 = 4, diff2 = 4

B diff1 = 4, diff2 = 5

C diff1 = 5, diff2 = 5

D diff1 = 5, diff2 = 4

💡 Hint

DATEDIFF counts date boundaries ignoring time, TIMESTAMPDIFF counts full days.

DATEDIFF counts the difference in days ignoring time parts, so it returns 5. TIMESTAMPDIFF counts full 24-hour days, so it returns 4.

🧠 Conceptual

expert

Understanding behavior of TIMESTAMPDIFF with negative intervals

What is the output of this query?

SELECT TIMESTAMPDIFF(DAY, '2024-06-15', '2024-06-10') AS days_diff;

MySQL

SELECT TIMESTAMPDIFF(DAY, '2024-06-15', '2024-06-10') AS days_diff;

A 5

B -5

C 0

D NULL

💡 Hint

TIMESTAMPDIFF returns negative values if the second date is earlier than the first.

TIMESTAMPDIFF returns the signed difference, so when the second date is before the first, it returns a negative number.