Back to list PostgreSQL How to Find the Interval Between Two Dates in PostgreSQL Database: PostgreSQL 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: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Extract the Week Number from a Date in PostgreSQL How to Find the Interval Between Two Dates in PostgreSQL How to Get the Day of the Year from a Date in PostgreSQL Tags: PostgreSQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.