Articles Cookbook
Back to list
SQLite

How to Calculate the Difference Between Two Dates in SQLite

Database:

Operators:

JULIANDAY

Problem:

You have two columns of the date type 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 arrival and departure and the number of days from arrival to departure inclusively.

The travel table looks like this:

iddeparturearrival
12018-03-252018-04-05
22019-09-122019-09-23
32018-07-142018-07-14
42018-01-052018-01-08

Solution:

SELECT
  id,
  departure,
  arrival,
  JULIANDAY(arrival) - JULIANDAY(departure) AS date_difference,
  JULIANDAY(arrival) - JULIANDAY(departure) + 1 AS days_inclusive
FROM travel;

The result is:

iddeparturearrivaldate_differencedays
12018-03-252018-04-051112
22019-09-122019-09-231112
32018-07-142018-07-1401
42018-01-052018-01-0834

Discussion:

To count the difference between dates, use the JULIANDAY() function, which counts the number of days since noon in Greenwich on November 24, 4714 B.C. You use the JULIANDAY(date) for each of the two dates; simply subtract them using a minus sign (-):

JULIANDAY(arrival) - JULIANDAY(departure)

In most cases, what you really want is the number of days from the first date to the second date inclusively. Then you need to add 1 day to the difference in days:

JULIANDAY(arrival) - JULIANDAY(departure) + 1.

Recommended courses:

Recommended articles:

See also:

go to top