PostgreSQL: How to Calculate Average Timestamp

Often in PostgreSQL you may want to calculate the average of values in a timestamp column.

You can use the following syntax to do so:

SELECT to_timestamp(avg(extract(epoch FROM start_time)))
FROM emp;

This particular example calculates the “average” of the timestamps in the start_time column of the emp table and returns the result as a timestamp.

Here is how this syntax works:

• First, we use extract(epoch FROM start_time) to extract the epoch (number of seconds since January 1, 1970) for each timestamp in the start_time column.
• Then, we use the avg() function to calculate the average epoch value.
• Lastly, we use the to_timestamp() function to convert this epoch value to a human readable timestamp.

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

Example: How to Calculate Average Timestamp in PostgreSQL

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

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

-- add rows to the table
INSERT INTO emp VALUES (0001, '2024-03-01 04:15:00.455');
INSERT INTO emp VALUES (0002, '2024-04-11 04:05:14.129');
INSERT INTO emp VALUES (0003, '2024-05-19 07:00:25.354');
INSERT INTO emp VALUES (0004, '2024-06-22 09:15:01.033');
INSERT INTO emp VALUES (0005, '2024-10-27 12:56:58.014');

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

 empid |       start_time        
-------+-------------------------
     1 | 2024-03-01 04:15:00.455
     2 | 2024-04-11 04:05:14.129
     3 | 2024-05-19 07:00:25.354
     4 | 2024-06-22 09:15:01.033
     5 | 2024-10-27 12:56:58.014

Suppose that we would like to calculate the average timestamp in the start_time column.

We can use the following syntax to do so:

SELECT to_timestamp(avg(extract(epoch FROM start_time)))
FROM emp;

        to_timestamp        
----------------------------
 2024-06-03 21:54:31.797+00

From the output we can see that the average timestamp in the start_time column is 2024-06-23 21:54:31.797.

Feel free to use similar syntax to calculate the average timestamp in a column of your own table in PostgreSQL.

Additional Resources

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

PostgreSQL: How to Select Timestamp without Seconds
PostgreSQL: How to Extract Time from a Timestamp
PostgreSQL: How to Add Hours to Timestamp
PostgreSQL: How to Add Minutes to a Timestamp