Often in PostgreSQL you may want to extract the hours and minutes from a timestamp.
You can use the following basic syntax to do so:
SELECT to_char(start_time,'HH:MI') AS hours_minutes
FROM emp;
This particular example extracts the hours and minutes from the timestamp in the start_time column of the table named emp and saves the results to a new column named hours_minutes.
Note that we use the to_char statement to convert the timestamp in the start_time column to a format that only includes the hours (HH) and minutes (MI) separated by a colon.
The following example shows how to use this syntax in practice.
Example: How to Extract Hours and Minutes 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 hours and minutes from each timestamp in the start_time column.
We can use the following syntax to do so:
SELECT empID,
to_char(start_time,'HH:MI') AS hours_minutes
FROM emp;
empid | hours_minutes
-------+---------------
1 | 04:15
2 | 04:05
3 | 07:00
4 | 09:15
5 | 12:56
The resulting table contains a new column named hours_minutes that contains only the hours and minutes from the original start_time column.
Note that you could also include the seconds from the timestamp by using the format HH:MI:SS instead:
SELECT empID,
to_char(start_time,'HH:MI:SS') AS hms
FROM emp;
empid | hms
-------+----------
1 | 04:15:00
2 | 04:05:00
3 | 07:00:00
4 | 09:15:00
5 | 12:56:00
The resulting table contains a new column named hms that contains the hours, minutes and seconds from the original start_time column.
Feel free to use whichever method you prefer.
Note: Make sure that you use MI (minutes) when formatting the hours and minutes instead of MM (months), which is a common mistake.
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
