Articles Cookbook
Back to list
PostgreSQL

How to Calculate the Difference Between Two Dates in PostgreSQL/Oracle

Database:

Operators:

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,
  arrival - departure AS date_difference,
  arrival - departure + 1 AS days
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 as days in PostgreSQL or Oracle, you simply need to subtract one date from the other, e.g. arrival - departure.

But 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: arrival - departure + 1.

Recommended courses:

Recommended articles:

See also:

go to top