Back to cookbooks list Articles Cookbook

How to Calculate Timestamp Difference in MySQL

  • TIMESTAMPDIFF()
  • WITH
  • MOD()
  • FLOOR()
  • CONCAT()

Problem:

You have two columns of the type timestamp and you want to calculate the difference between them.

Example:

In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between the arrival and the departure.

The travel table looks like this:

iddeparturearrival
12018-03-25 12:00:002018-04-05 07:30:00
22019-09-12 15:50:002019-10-23 10:30:30
32018-07-14 16:15:002018-07-14 20:40:30
42018-01-05 08:35:002019-01-08 14:00:00

Solution 1 (difference in days, hours, minutes, or seconds):

SELECT
  id,
  departure,
  arrival,
  TIMESTAMPDIFF(SECOND, departure, arrival) AS difference
FROM travel;

The result is:

iddeparturearrivaldifference
12018-03-25 12:00:002018-04-05 07:30:00934200
22019-09-12 15:50:002019-10-23 10:30:303523230
32018-07-14 16:15:002018-07-14 20:40:3015930
42018-01-05 08:35:002019-01-08 14:00:0031814700

Discussion:

To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF(unit, start, end) function. The unit argument can be MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. To get the difference in seconds as we have done here, choose SECOND. To get the difference in minutes, choose MINUTE; for the difference in hours, choose HOUR, etc. The end and the start arguments are the ending timestamp and the starting timestamp, respectively (here, departure and arrival, respectively).

Discover the best interactive MySQL courses

Solution 2 (difference in days, hours, minutes, and seconds):

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

The result is:

iddeparturearrivaldifference
12018-03-25 12:00:002018-04-05 07:30:0010 days 19 hours 30 minutes 0 seconds
22019-09-12 15:50:002019-10-23 10:30:3040 days 18 hours 40 minutes 30 seconds
32018-07-14 16:15:002018-07-14 20:40:300 days 4 hours 25 minutes 30 seconds
42018-01-05 08:35:002019-01-08 14:00:00368 days 5 hours 25 minutes 0 seconds

Discussion:

First, calculate the difference between the timestamps in seconds, using the TIMESTAMPDIFF() function (the first CTE, named difference_in_seconds), just as in Solution 1. Calculate how many seconds there are in excess of whole minutes (seconds_part) to be used later to calculate the seconds, how many seconds there are in excess of whole hours (minutes_part) to be used later to calculate the minutes, and how many seconds there are in excess of whole hours (hours_part) to be used later to calculate the hours.

To do this, use the MOD() function. For example, an hour has 3600 seconds, so to find how many seconds there are in minutes_part, find the remainder from the division by 3600 like this:

MOD(seconds, 3600) AS minutes_part

Similarly, there are 3600 * 24 seconds in a day, so to calculate how many seconds there are in hours_part, write:

MOD(seconds, 3600 * 24) AS hours_part

Once these remainders are calculated (in the second CTE, named differences), you can finally get the difference in days, hours, minutes, and seconds. To get the number of seconds, minutes, hours, and days, divide the number of seconds in the remainder by the corresponding number of seconds in days, hours, or minutes. For example, to find out how many minutes should be displayed, take minutes_part and divide it by 60, since there are 60 minutes in an hour. You only need the integer part from this (i.e., without the decimal part), so use the FLOOR() function like this:

FLOOR(minutes_part / 60)

Finally, you simply need to display in one string what you've calculated. To do this, use the CONCAT() function in the outer query:

CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference

The solution presented here returns the last column as text. You can easily modify this solution to display it in some other format. You can also display the numbers in separate columns, like this:

FLOOR(seconds / 3600 / 24) AS days,
FLOOR(hours_part / 3600) AS hours,
FLOOR(minutes_part / 60) AS minutes,
seconds_part AS seconds

Recommended courses:

Recommended articles:

See also: