PostgreSQL: Calculate Timestamp Difference in Hours

Often in PostgreSQL you may want to calculate the difference between two timestamps in terms of hours.

You can use the following basic syntax to do so:

SELECT extract(epoch FROM (end_time - start_time))/3600 AS hour_diff
FROM emp;

This particular example calculates the difference (in hours) 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 hour_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.
• Lastly, we divide the result by 3,600, which is the number of seconds in an hour.

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

Example: How to Calculate Timestamp Difference in Hours 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:001

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

We can use the following syntax to do so:

SELECT empID,
       extract(epoch FROM (end_time - start_time))/3600 AS hour_diff
FROM emp;

 empid |       hour_diff        
-------+------------------------
     1 |     3.3166666666666667
     2 |    10.5500000000000000
     3 |     4.7500000000000000
     4 | 0.78333333333333333333
     5 |     2.0333333333333333

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

For example, we can see:

• Employee 1 worked a total of 3.3167 hours on the task.
• Employee 2 worked a total of 10.55 hours on the task.
• Employee 3 worked a total of 4.75 hours on the task.

And so on.

Note that you could also convert the result to an int to only display the number of integer hours:

SELECT empID,
       (extract(epoch FROM (end_time - start_time))/3600)::int AS hour_diff
FROM emp;

 empid | hour_diff 
-------+-----------
     1 |         3
     2 |        11
     3 |         5
     4 |         1
     5 |         2

The hour_diff column now shows the number of hours as an integer with no decimal places.

Feel free to use whichever method you prefer.

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