Skip to content
MySQL

Calculating the Difference Between Two Dates in MySQL

3 min read

The other day I had to quickly grab some statistics from a database table. I needed to determine how long jobs in a queue system were taking to run on average. In order to do this, I made use of MySQL’s time difference methods. I thought I'd share how these methods work, and how I implemented them.

To calculate the difference between two dates, MySQL offers us two different methods: TIMESTAMPDIFF and TIMEDIFF. Which you use depends how you want the resulting difference formatted.

TIMESTAMPDIFF

This method returns the difference between two dates in the units supplied as the first parameter. For example, if we want to know the difference between two dates in seconds:

SELECT id, job, TIMESTAMPDIFF(SECOND, start_date, end_date) AS runtime
    FROM example_table;

This calculates end_date - start_date in seconds.

The units can be any one of the following : FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

The dates passed to this method do not need to be of the same type. One could be a DATETIME and the other a DATE. If the column has a DATE type it will be assumed to have a time of midnight.

TIMEDIFF

This method returns the difference between two dates formatted as hours:minutes:seconds.microseconds.

SELECT id, job, TIMEDIFF(end_date, start_date) AS runtime
    FROM example_table;

Like TIMESTAMPDIFF, this calculates end_date - start_date, but note that the date parameters are called in the opposite order.

Unlike TIMESTAMPDIFF, both dates need to be of the same type.

A real world example

Now that we’ve established how to use MySQL’s time difference methods, let’s look at a real use case.

As I mentioned at the beginning I needed to check how long my queue was taking to process jobs. I have a queued_tasks table which contains details of background tasks. Using TIMEDIFF I was able to determine the longest and shortest running jobs during the last month:

SELECT MAX(TIMEDIFF(completed, fetched)) AS max_runtime, MIN(TIMEDIFF(completed, fetched)) AS min_runtime
    FROM queue
    WHERE completed IS NOT NULL AND created > NOW() - INTERVAL 1 MONTH;

This gave me the range of the runtimes for my queue.

I was then able to use TIMESTAMPDIFF to calculate the average time, using the range I just calculated to choose an appropriate time unit:

 SELECT AVG(TIMESTAMPDIFF(MINUTE, fetched, completed)) AS average_runtime
    FROM queue
    WHERE completed IS NOT NULL AND created > NOW() - INTERVAL 1 MONTH;

A quick and simple solution for determining some basic statistics about my queue.

Check out MySQL's date and time functions for more information.

© 2024 Andy Carter