# How to Calculate Timestamp Difference in MySQL

Database:

Operators:

## Problem:

You have two columns of the type `timestamp`

and you want to calculate the difference between them.

## Example:

In the

table, there are three columns: **travel**`id`

, `departure`

, and `arrival`

. You'd like to calculate the difference between the `arrival`

and the `departure`

.

The

table looks like this:**travel**

id | departure | arrival |
---|---|---|

1 | 2018-03-25 12:00:00 | 2018-04-05 07:30:00 |

2 | 2019-09-12 15:50:00 | 2019-10-23 10:30:30 |

3 | 2018-07-14 16:15:00 | 2018-07-14 20:40:30 |

4 | 2018-01-05 08:35:00 | 2019-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:

id | departure | arrival | difference |
---|---|---|---|

1 | 2018-03-25 12:00:00 | 2018-04-05 07:30:00 | 934200 |

2 | 2019-09-12 15:50:00 | 2019-10-23 10:30:30 | 3523230 |

3 | 2018-07-14 16:15:00 | 2018-07-14 20:40:30 | 15930 |

4 | 2018-01-05 08:35:00 | 2019-01-08 14:00:00 | 31814700 |

## 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`

).

## 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:

id | departure | arrival | difference |
---|---|---|---|

1 | 2018-03-25 12:00:00 | 2018-04-05 07:30:00 | 10 days 19 hours 30 minutes 0 seconds |

2 | 2019-09-12 15:50:00 | 2019-10-23 10:30:30 | 40 days 18 hours 40 minutes 30 seconds |

3 | 2018-07-14 16:15:00 | 2018-07-14 20:40:30 | 0 days 4 hours 25 minutes 30 seconds |

4 | 2018-01-05 08:35:00 | 2019-01-08 14:00:00 | 368 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