PostgreSQL: Calculate Timestamp Difference in Minutes

Often in PostgreSQL you may want to calculate the difference between two timestamps in minutes.

You can use the following basic syntax to do so:

SELECT (extract(epoch FROM (end_time - start_time))/60)::int AS diff
FROM emp;

This particular example calculates the number of minutes between corresponding timestamps in the start_time and end_time columns of the table named emp and saves the results to a new column named diff.

Here is how this syntax works:

• First, we subtract the time in the start_time column from the time in the end_time column.
• Next, we use the extract statement to extract the epoch from this time difference, which returns the time difference in seconds.
• Then, we divide the result by 60 to convert seconds to minutes.
• Lastly, we use int to convert the result to an integer.

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

Example: How to Calculate Timestamp Difference in Minutes in PostgreSQL

Suppose that we create the following table named emp that contains information about when various employees started and stopped working on some specific task:

-- create table named 'emp'
CREATE TABLE emp (
  empID INT  PRIMARY KEY,
  start_time TIMESTAMP,
  end_time TIMESTAMP
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2024-09-01 04:15:00', '2024-09-01 07:34:00');
INSERT INTO emp VALUES (0002, '2024-09-01 04:05:00', '2024-09-01 14:38:00');
INSERT INTO emp VALUES (0003, '2024-09-01 07:00:00', '2024-09-01 11:45:00');
INSERT INTO emp VALUES (0004, '2024-09-01 09:15:00', '2024-09-01 10:02:00');
INSERT INTO emp VALUES (0005, '2024-09-01 12:56:00', '2024-09-01 14:58:00');

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

 empid |     start_time      |      end_time       
-------+---------------------+---------------------
     1 | 2024-09-01 04:15:00 | 2024-09-01 07:34:00
     2 | 2024-09-01 04:05:00 | 2024-09-01 14:38:00
     3 | 2024-09-01 07:00:00 | 2024-09-01 11:45:00
     4 | 2024-09-01 09:15:00 | 2024-09-01 10:02:00
     5 | 2024-09-01 12:56:00 | 2024-09-01 14:58:00

Suppose that we would like to calculate the difference between the start_time and end_time values for each employee, in terms of minutes.

We can use the following syntax to do so:

SELECT empID,
       extract(epoch FROM (end_time - start_time))/60 AS diff
FROM emp;

 empid | diff 
-------+------
     1 |  199
     2 |  633
     3 |  285
     4 |   47
     5 |  122

The resulting table contains a new column named diff that shows the number of minutes that each employee worked on the specific task.

For example, we can see:

• Employee 1 worked a total of 199 minutes on the task.
• Employee 2 worked a total of 633 minutes on the task.
• Employee 3 worked a total of 285 minutes on the task.

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: Calculate Difference Between Dates in Days
PostgreSQL: How to Calculate Difference Between Dates in Weeks
PostgreSQL: How to Calculate Difference Between Dates in Months