Articles Cookbook
Back to list
PostgreSQL

How to Find the Interval Between Two Dates in PostgreSQL

Database:

Operators:

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
1BarbaraWilson2010-02-012018-10-30
2RobertAnderson2001-04-172011-12-20
3StevenNelson2005-06-012019-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 2005-06-01, when he started this job, and 2019-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 ‘2019-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 2019-09-26).

Solution 2:

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

Recommended courses:

Recommended articles:

See also:

go to top