How to Calculate Date Difference in PostgreSQL/Oracle

Problem:

You have two columns of the date type and you want to calculate the difference between them in PostgreSQL or Oracle database.

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:

Solution:

SELECT
  id,
  departure,
  arrival,
  arrival - departure AS date_difference,
  arrival - departure + 1 AS days
FROM travel;

The result is:

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.