PostgreSQL: Calculate Timestamp Difference in Milliseconds

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

You can use the following basic syntax to do so:

SELECT extract(epoch FROM (end_time - start_time))*1000 AS diff
FROM emp;

This particular example calculates the number of milliseconds 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.
• Lastly, we multiply by 1,000 to convert seconds to milliseconds.

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

Example: How to Calculate Timestamp Difference in Milliseconds 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 04:15:00.455');
INSERT INTO emp VALUES (0002, '2024-09-01 04:05:00', '2024-09-01 04:05:00.12');
INSERT INTO emp VALUES (0003, '2024-09-01 07:00:00', '2024-09-01 07:00:00.354');
INSERT INTO emp VALUES (0004, '2024-09-01 09:15:00', '2024-09-01 09:15:01.033');
INSERT INTO emp VALUES (0005, '2024-09-01 12:56:00', '2024-09-01 12:56:00.014');

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

 empid |       start_time       |        end_time         
-------+------------------------+-------------------------
     1 | 2024-09-01 04:15:00.02 | 2024-09-01 04:15:00.455
     2 | 2024-09-01 04:05:00    | 2024-09-01 04:05:00.12
     3 | 2024-09-01 07:00:00    | 2024-09-01 07:00:00.354
     4 | 2024-09-01 09:15:00    | 2024-09-01 09:15:01.033
     5 | 2024-09-01 12:56:00    | 2024-09-01 12:56:00.014

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

We can use the following syntax to do so:

SELECT empID,
       extract(epoch FROM (end_time - start_time))*1000 AS diff
FROM emp;

 empid |    diff     
-------+-------------
     1 |  435.000000
     2 |  120.000000
     3 |  354.000000
     4 | 1033.000000
     5 |   14.000000

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

For example, we can see:

• Employee 1 worked a total of 435 milliseconds on the task.
• Employee 2 worked a total of 120 milliseconds on the task.
• Employee 3 worked a total of 354 milliseconds 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