PostgreSQL: How to Subtract Hours from a Timestamp

Often in PostgreSQL you may want to subtract a specific number of hours from a timestamp.

You can use the following basic syntax to do so:

SELECT *,
       ((start_time) - INTERVAL '1 HOUR') AS one_before
FROM emp;

This particular example subtracts 1 hour from the timestamps in the start_time column of the table named emp and saves the results to a column named one_before.

Note: To subtract a different number of hours, simply change 1 HOUR to a different number, e.g. 3 HOUR.

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

Example: How to Subtract Hours from a Timestamp in PostgreSQL

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

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

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2024-09-23 04:15:23');
INSERT INTO emp VALUES (0002, '2024-04-01 04:05:56');
INSERT INTO emp VALUES (0003, '2024-10-26 07:00:10');
INSERT INTO emp VALUES (0004, '2024-12-14 09:15:00');
INSERT INTO emp VALUES (0005, '2024-12-07 12:56:30');

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

 empid |     start_time      
-------+---------------------
     1 | 2024-09-23 04:15:23
     2 | 2024-04-01 04:05:56
     3 | 2024-10-26 07:00:10
     4 | 2024-12-14 09:15:00
     5 | 2024-12-07 12:56:30

Suppose that we would like to create a new column that subtracts one hour from each timestamp in the start_time column.

We can use the following syntax to do so:

SELECT *,
       ((start_time) - INTERVAL '1 HOUR') AS one_before
FROM emp;

 empid |     start_time      | sales |     one_before      
-------+---------------------+-------+---------------------
     1 | 2024-09-23 04:15:00 |   134 | 2024-09-23 03:15:00
     2 | 2024-04-01 04:05:00 |   201 | 2024-04-01 03:05:00
     3 | 2024-10-26 07:00:00 |   155 | 2024-10-26 06:00:00
     4 | 2024-12-14 09:15:00 |    98 | 2024-12-14 08:15:00
     5 | 2024-12-07 12:56:00 |    67 | 2024-12-07 11:56:00

The resulting table contains a new column named one_before that contains the timestamp from the start_time column with one hour subtracted from it.

Feel free to use similar syntax to subtract a specific number of hours from a timestamp column in your own table.

Additional Resources

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

PostgreSQL: Calculate Timestamp Difference in Hours
PostgreSQL: Calculate Timestamp Difference in Minutes
PostgreSQL: Calculate Difference Between Dates in Days