How to Find the Interval Between Two Dates in PostgreSQL

Problem:

You’d like to find the difference between two date/datetime values in a PostgreSQL database.

Example:

Our database has a table named employment with data in the columns id, first_name, last_name, start_date, and end_date:

For each employee, let’s get their first and last name and the difference between the starting and ending dates of their employment. We want to see the interval in years, months, and days.

Solution 1:

We’ll use the AGE() function. Here’s the query you would write:

SELECT 
  first_name, 
  last_name, 
  AGE(end_date, start_date) AS employment_interval 
FROM employment;

Here’s the result of the query:

Discussion:

Use the PostgreSQL AGE() function to retrieve the interval between two timestamps or dates. This function takes two arguments: the first is the end date and the second is the start date. In our example, we use the column end_date (i.e. when the employee stopped doing that job) and the column start_date (when the employee started that job).

The difference between dates is returned as an interval in years, months, days, hours, etc. The query for Steven Nelson returned the period of employment as the interval 14 years 3 months 22 days; this is the difference between 2010-06-01, when he started this job, and 2023-09-23, when he stopped it.

The AGE() function can also display the difference between the current timestamp/date and the first argument. In this case, the function has only one argument:

SELECT 
  first_name,
  last_name,
  AGE(end_date) AS employment_interval
FROM employment;

The query above displays the interval between the current timestamp (for this text, it is 2023-09-26) and each employee’s end date (the column end_date).

Three days have elapsed between Steven’s last day on the job and the current timestamp (at the time of writing, that’s 2023-09-26).

Solution 2:

In PostgreSQL you can also use the minus operator (-) instead of AGE() to subtract two dates.

Here’s the query you’d write:

SELECT 
  first_name,
  last_name,
  end_date::DATE – start_date::DATE AS employment_interval 
FROM employment;

In this result, you’ll only see the difference in days (not years, months, and days):