PostgreSQL: Calculate Difference Between Dates in Weeks

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

You can use the following basic syntax to do so:

SELECT ((extract(epoch FROM date_trunc('week', end_date)) -
         extract(epoch FROM date_trunc('week', start_date))
       )/604800)::int AS week_diff
FROM emp;

This particular example calculates the difference (in weeks) 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 week_diff.

Note that we first use the epoch command to calculate the number of seconds it has been since the epoch (January 1, 1970) for the end_date.

Then we subtract the number of seconds it has been since the epoch for the start_date, then divide by 604,800 (number of seconds in a week) to get the week difference.

Lastly, we convert the result to an int to remove all of the trailing zeros after the decimal place.

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

Example: How to Calculate Difference Between Dates in Weeks 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 weeks.

We can use the following syntax to do so:

SELECT emp_id,
       ((extract(epoch FROM date_trunc('week', end_date)) -
         extract(epoch FROM date_trunc('week', start_date))
       )/604800)::int AS week_diff
FROM emp;

 empid | week_diff 
-------+-----------
     1 |       111
     2 |        45
     3 |        48
     4 |        35
     5 |         1

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

For example, we can see:

• Employee 1 worked a total of 111 weeks at the company.
• Employee 2 worked a total of 45 weeks at the company.
• Employee 3 worked a total of 48 weeks at the company.

And so on.

Note: This method calculates the number of whole weeks between the start and end dates.

Additional Resources

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

PostgreSQL: How to Calculate Difference Between Rows
PostgreSQL: Calculate Difference Between Dates in Months
PostgreSQL: How to Select the Last Row per Group