# How to Calculate the Difference Between Two Timestamps in SQLite

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

SELECT id, departure, arrival, JULIANDAY(arrival) - JULIANDAY(departure) AS difference FROM travel;

The result is:

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

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

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

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

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

## Discussion:

To calculate the difference between the timestamps in SQLite, use the `JULIANDAY()`

function for both timestamps, then subtract one from the other. This way, you get the difference in days. The integer part of the difference is the number of full days, and the decimal part represents a partial day.

The `JULIANDAY()`

function returns the number of days since noon Greenwich Mean Time on November 24, 4714 B.C. You can read more about this function in the documentation.

## Solution 2 (difference in seconds):

SELECT id, departure, arrival, ROUND((JULIANDAY(arrival) - JULIANDAY(departure)) * 86400) 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:

If you'd like to calculate the difference between the timestamps in seconds, multiply the decimal difference in days by the number of seconds in a day, which equals `24 * 60 * 60 = 86400`

, or the product of the number of hours in a day, the number of minutes in an hour, and the number of seconds in a minute. Since the decimal parts for difference in days aren't 100% accurate due to rounding errors, you may get a non-zero decimal part. Rounding the result to the nearest integer, using the `ROUND()`

function, fixes the problem.

Similarly, you could calculate the difference in minutes or in hours. Just change `* 86400 to * 3600`

(minutes) or `* 60`

(hours).