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:
TIMEDIFF. Which you use depends how you want the resulting difference formatted.
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;
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.
This method returns the difference between two dates formatted as
SELECT id, job, TIMEDIFF(end_date, start_date) AS runtime FROM example_table;
TIMESTAMPDIFF, this calculates
end_date - start_date, but note that the date parameters are called in the opposite order.
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.