PostgreSQL: How to Extract Hour from Timestamp

Often in PostgreSQL you may want to extract the hour from a timestamp.

You can use the following basic syntax to do so:

SELECT extract(hour FROM start_time) AS hour_start
FROM emp;

This particular example extracts the hour from the timestamp in the start_time column of the table named emp and saves the results to a new column named hour_start.

Here is how this syntax works:

• First, we use the extract statement to extract the hour from the start_time column.
• Then we use the AS statement to save this hour to a new column.

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

Example: How to Extract Hour from Timestamp 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 extract only the hour value from each timestamp in the start_time column.

We can use the following syntax to do so:

SELECT empID,
       extract(hour FROM start_time) AS hour_start
FROM emp;

 empid | hour_start 
-------+------------
     1 |          4
     2 |          4
     3 |          7
     4 |          9
     5 |         12

The resulting table contains a new column named hour_start that contains the hour that each employee started working on the specific task.

For example, we can see:

• Employee 1 started working on the task at hour 4.
• Employee 2 started working on the task at hour 4.
• Employee 3 started working on the task at hour 7.

And so on.

Feel free to use similar syntax to extract the hour from a timestamp in your own table.

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