DATEDIFF and TIMESTAMPDIFF in MySQL

Introduction

We use DATEDIFF and TIMESTAMPDIFF to find the difference between two dates or times. This helps us understand how much time passed between events.

To find how many days a project took from start to finish.

To calculate a person's age in years from their birthdate.

To check how many hours passed between two timestamps in a log.

To find the number of months between two payment dates.

To measure the difference in minutes between two event times.

Syntax

MySQL

DATEDIFF(date1, date2)

TIMESTAMPDIFF(unit, datetime1, datetime2)

DATEDIFF returns the number of days between two dates.

TIMESTAMPDIFF lets you choose the unit like SECOND, MINUTE, HOUR, DAY, MONTH, or YEAR.

Examples

This returns the number of days between June 1 and June 10, 2024.

MySQL

SELECT DATEDIFF('2024-06-10', '2024-06-01');

This returns the number of full months between January 1, 2023 and June 1, 2024.

MySQL

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

This returns the number of hours between 8 AM and 3:30 PM on June 10, 2024.

MySQL

SELECT TIMESTAMPDIFF(HOUR, '2024-06-10 08:00:00', '2024-06-10 15:30:00');

Sample Program

This query shows how to use DATEDIFF and TIMESTAMPDIFF to find differences in days, hours, and minutes between two dates or timestamps.

MySQL

SELECT
  DATEDIFF('2024-06-15', '2024-06-10') AS days_diff,
  TIMESTAMPDIFF(DAY, '2024-06-10 08:00:00', '2024-06-15 20:00:00') AS days_diff_timestamp,
  TIMESTAMPDIFF(HOUR, '2024-06-10 08:00:00', '2024-06-10 20:00:00') AS hours_diff,
  TIMESTAMPDIFF(MINUTE, '2024-06-10 08:00:00', '2024-06-10 08:30:00') AS minutes_diff;

Important Notes

DATEDIFF only counts full days and ignores time parts.

TIMESTAMPDIFF counts full units (like full hours or full minutes) between two timestamps.

For positive results, use DATEDIFF(later, earlier) or TIMESTAMPDIFF(unit, earlier, later).

Summary

Use DATEDIFF to find days between two dates.

Use TIMESTAMPDIFF to find difference in various units like seconds, minutes, hours, days, months, or years.

Both help measure time passed between events easily.