How to Calculate Date Difference in PostgreSQL/Oracle
Database:
Operators:
Table of Contents
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:
| id | departure | arrival |
|---|---|---|
| 1 | 2018-03-25 | 2018-04-05 |
| 2 | 2019-09-12 | 2019-09-23 |
| 3 | 2018-07-14 | 2018-07-14 |
| 4 | 2018-01-05 | 2018-01-08 |
Solution
SELECT id, departure, arrival, arrival - departure AS date_difference, arrival - departure + 1 AS days FROM travel;
The result is:
| id | departure | arrival | date_difference | days |
|---|---|---|---|---|
| 1 | 2018-03-25 | 2018-04-05 | 11 | 12 |
| 2 | 2019-09-12 | 2019-09-23 | 11 | 12 |
| 3 | 2018-07-14 | 2018-07-14 | 0 | 1 |
| 4 | 2018-01-05 | 2018-01-08 | 3 | 4 |
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.