PostgreSQL: Calculate Difference Between Dates in Months

Often in PostgreSQL you may want to calculate the difference between two dates in terms of months.

You can use the following basic syntax to do so:

SELECT extract(year FROM age(end_date, start_date)) * 12 +
       extract(month FROM age(end_date, start_date)) AS month_diff
FROM emp;

This particular example calculates the difference (in months) between corresponding values in the start_date and end_date columns of the table named emp and saves the results to a new column named month_diff.

Note that we first use the age command with the year statement to extract the total year difference (and multiply it by 12 to convert to months) between the two dates.

We then use the age command with the month statement to extract the month difference between the dates.

We then add these two values together to get the total month difference between the dates.

The following example shows how to use this syntax in practice.

Example: How to Calculate Difference Between Dates in Months in PostgreSQL

Suppose that we create the following table named emp that contains information about when various employees started working and ended working at some company:

-- create table named 'emp'
CREATE TABLE emp (
  empID INT  PRIMARY KEY,
  start_date DATE,
  end_date DATE
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2022-04-01', '2024-05-14');
INSERT INTO emp VALUES (0002, '2022-07-01', '2023-05-14');
INSERT INTO emp VALUES (0003, '2022-08-15', '2023-07-17');
INSERT INTO emp VALUES (0004, '2023-12-01', '2024-08-01');
INSERT INTO emp VALUES (0005, '2024-11-23', '2024-12-01');

-- view all rows in table
SELECT * FROM emp;

 empid | start_date |  end_date  
-------+------------+------------
     1 | 2022-04-01 | 2024-05-14
     2 | 2022-07-01 | 2023-05-14
     3 | 2022-08-15 | 2023-07-17
     4 | 2023-12-01 | 2024-08-01
     5 | 2024-11-23 | 2024-12-01

Suppose that we would like to calculate the difference between the start_date and end_date values for each employee, in terms of total number of months.

We can use the following syntax to do so:

SELECT empID, 
       extract(year FROM age(end_date, start_date)) * 12 +
       extract(month FROM age(end_date, start_date)) AS month_diff
FROM emp;

 empid | month_diff 
-------+------------
     1 |         25
     2 |         10
     3 |         11
     4 |          8
     5 |          0

The resulting table contains a new column named month_diff that shows the total number of months that each employee worked at the company.

For example, we can see:

• Employee 1 worked a total of 25 months at the company.
• Employee 2 worked a total of 10 months at the company.
• Employee 3 worked a total of 11 months at the company.

And so on.

Note that this method calculates the number of whole months between the start and end dates.

Since the last employee in the table didn’t work at the company for at least one month, they have a value of 0 in the month_diff column.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Calculate Difference Between Rows
PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to SELECT DISTINCT on Multiple Columns