DATEDIFF() and TIMEDIFF() in MySQL 8: Get the difference between two dates/times

Introduction

Working with dates and times is a common scenario in database management, and MySQL offers robust functions to handle this. Two of the most useful functions when dealing with date and time calculations are DATEDIFF() and TIMEDIFF(). In this tutorial, we will explore how to use these MySQL functions to calculate differences between dates and times, providing various examples to demonstrate their functionality.

Understanding DATEDIFF()

DATEDIFF() is a MySQL function that calculates the number of days between two date values. The syntax is simple:

DATEDIFF(date1, date2)

It returns the number of days between date1 and date2. If date1 is later than date2, the result is positive. If date1 is earlier, the result is negative.

Basic Examples

Let’s start with some basic examples:

SELECT DATEDIFF('2023-05-01', '2023-04-01') AS DiffDays; -- Output: 30
SELECT DATEDIFF('2023-04-01', '2023-05-01') AS DiffDays; -- Output: -30

As you can see, the second query returns a negative number since the first date is earlier than the second one.

Handling NULL Values

If one of the dates is NULL, DATEDIFF() returns NULL. Let’s verify this with an example:

SELECT DATEDIFF('2023-05-01', NULL) AS DiffDays; -- Output: NULL

Using DATEDIFF() with Real Data

Now let’s apply DATEDIFF() in a real-world scenario. Assume we have a table projects with columns start_date and end_date:

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100),
    start_date DATE,
    end_date DATE
);

INSERT INTO projects (project_id, project_name, start_date, end_date) VALUES
(1, 'Project A', '2023-01-01', '2023-03-01'),
(2, 'Project B', '2023-02-15', '2023-04-15'),
(3, 'Project C', '2023-03-20', '2023-06-20');

To calculate the duration of each project, we can run:

SELECT project_name, DATEDIFF(end_date, start_date) AS DurationDays
FROM projects;

This will output the number of days for each project’s duration.

Understanding TIMEDIFF()

TIMEDIFF() function is used to calculate the difference between two time values. Unlike DATEDIFF(), which only works with dates, TIMEDIFF() works with both time and datetime values. The syntax for TIMEDIFF() is:

TIMEDIFF(time1, time2)

The function returns the difference in time format: 'HH:MM:SS' or 'HHH:MM:SS' for large hour values.

Basic Examples

Here are some straightforward uses of TIMEDIFF():

SELECT TIMEDIFF('12:00:00', '08:00:00') AS TimeDiff; -- Output: '04:00:00'
SELECT TIMEDIFF('2023-03-01 14:00:00', '2023-03-01 12:30:00') AS TimeDiff; -- Output: '01:30:00'

The first example shows the difference in hours between two time values. The second example includes datetime values.

Handling NULL Values and Negative Differences

When working with TIMEDIFF(), a NULL input will yield a NULL result, just as with DATEDIFF(). Negative time values are expressed as negative durations:

SELECT TIMEDIFF('08:00:00', '12:00:00') AS TimeDiff; -- Output: '-04:00:00'

Advanced Examples

We can use these functions to solve more complex problems. Let’s say we want to calculate the overtime hours of employees on a particular day:

CREATE TABLE timesheet (
    employee_id INT,
    work_date DATE,
    start_time TIME,
    end_time TIME,
    scheduled_hours INT
);

INSERT INTO timesheet (employee_id, work_date, start_time, end_time, scheduled_hours) VALUES
(1, '2023-03-15', '09:00:00', '18:30:00', 8);

SELECT employee_id, work_date,
    TIMEDIFF(end_time, start_time) AS TotalTime,
    TIMEDIFF(TIMEDIFF(end_time, start_time), SEC_TO_TIME(scheduled_hours * 3600)) AS Overtime
FROM timesheet;

The Overtime column shows the difference between the total time worked and the scheduled hours, correctly giving the overtime hours.

Combining DATEDIFF() and TIMEDIFF()

Sometimes, you may need to combine both DATEDIFF() and TIMEDIFF() to calculate precise time intervals involving both dates and times. Consider a scenario where events have a start and end timestamp, and you want to calculate precisely how long the events took:

CREATE TABLE events (
    event_id INT,
    start_timestamp DATETIME,
    end_timestamp DATETIME
);

INSERT INTO events (event_id, start_timestamp, end_timestamp) VALUES
(1, '2023-03-01 10:00:00', '2023-03-03 14:00:00');

SELECT event_id,
    CONCAT(
        DATEDIFF(end_timestamp, start_timestamp), ' days ',
        TIMEDIFF(end_timestamp, start_timestamp)
    ) AS EventDuration
FROM events;

In this setup, EventDuration will give both the number of days and the precise time difference, ensuring that full accuracy is preserved.

Conclusion

In conclusion, both DATEDIFF() and TIMEDIFF() functions in MySQL 8 are essential for performing date and time interval calculations. Whether it’s determining the number of days between dates or the time difference between time values, these functions are reliable tools that can be utilized to manage and analyze temporal data efficiently.