PostgreSQL: Calculate Difference Between Dates in Days

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

You can use the following basic syntax to do so:

SELECT extract(day FROM end_date::timestamp - start_date::timestamp) AS diff
FROM emp;

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

Note that we first convert each date to a timestamp, then subtract the starting date from the ending date, then use the extract command to extract only the day component from the time difference.

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

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

Suppose that we create the following table named emp that contains information about when various employees started and stopped 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, '2024-04-01', '2024-05-14');
INSERT INTO emp VALUES (0002, '2023-07-01', '2023-08-14');
INSERT INTO emp VALUES (0003, '2023-02-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 | 2024-04-01 | 2024-05-14
     2 | 2023-07-01 | 2023-08-14
     3 | 2023-02-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 days.

We can use the following syntax to do so:

SELECT empID,
       extract(day FROM end_date::timestamp - start_date::timestamp) AS diff
FROM emp;

 empid | diff 
-------+------
     1 |   43
     2 |   44
     3 |  152
     4 |  244
     5 |    8

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

For example, we can see:

• Employee 1 worked a total of 43 days at the company.
• Employee 2 worked a total of 44 days at the company.
• Employee 3 worked a total of 152 days at the company.

And so on.

Additional Resources

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

PostgreSQL: How to Calculate Difference Between Rows
PostgreSQL: How to Calculate Difference Between Dates in Weeks
PostgreSQL: How to Calculate Difference Between Dates in Months