# How to Find the Interval Between Two Dates in PostgreSQL

• AGE()
• -

## 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`:

idfirst_namelast_namestart_dateend_date
1BarbaraWilson2015-02-012023-10-30
2RobertAnderson2006-04-172016-12-20
3StevenNelson2010-06-012023-09-23

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:

first_namelast_nameemployment_interval
BarbaraWilson8 years 8 months 29 days
RobertAnderson10 years 8 months 3 days
StevenNelson14 years 3 months 22 days

## 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`).

first_namelast_nameemployment_interval
BarbaraWilson10 months 27 days
RobertAnderson7 years 9 months 6 days
StevenNelson3 days

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):

first_namelast_nameemployment_interval
BarbaraWilson3193
RobertAnderson3899
StevenNelson5227