Often in PostgreSQL you may want to select a timestamp without the seconds.
You can use the date_trunc() function with the following syntax to do so:
SELECT *,
date_trunc('minute', start_time) AS no_seconds
FROM emp;
This particular example creates a new column named no_seconds that removes the seconds from the timestamp in the column named start_time.
Note that we use the date_trunc() function with ‘minute’ as the unit of time to indicate that the datetime should be truncated at minutes, i.e. everything after the minutes should be removed.
The following example shows how to use this syntax in practice.
Example: How to Select Timestamp without Seconds 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-09-01 04:15:00.455');
INSERT INTO emp VALUES (0002, '2024-09-01 04:05:14.129');
INSERT INTO emp VALUES (0003, '2024-09-01 07:00:25.354');
INSERT INTO emp VALUES (0004, '2024-09-01 09:15:01.033');
INSERT INTO emp VALUES (0005, '2024-09-01 12:56:58.014');
-- view all rows in table
SELECT * FROM emp;
empid | start_time
-------+-------------------------
1 | 2024-09-01 04:15:00.455
2 | 2024-09-01 04:05:14.129
3 | 2024-09-01 07:00:25.354
4 | 2024-09-01 09:15:01.033
5 | 2024-09-01 12:56:58.014
We can use the following syntax to select each of the timestamps from the start_time column without the seconds:
SELECT *,
date_trunc('minute', start_time) AS no_seconds
FROM emp;
empid | start_time | no_seconds
-------+-------------------------+---------------------
1 | 2024-09-01 04:15:00.455 | 2024-09-01 04:15:00
2 | 2024-09-01 04:05:14.129 | 2024-09-01 04:05:00
3 | 2024-09-01 07:00:25.354 | 2024-09-01 07:00:00
4 | 2024-09-01 09:15:01.033 | 2024-09-01 09:15:00
5 | 2024-09-01 12:56:58.014 | 2024-09-01 12:56:00
Notice that the new column named no_seconds contains the timestamp from the start_time column with the seconds removed.
If you would like to display the timestamp without the seconds, you could use the to_char() function with the following syntax:
SELECT *,
to_char(start_time, 'YYYY-MM-DD HH24:MI') AS no_seconds
FROM emp;
empid | start_time | no_seconds
-------+-------------------------+------------------
1 | 2024-09-01 04:15:00.455 | 2024-09-01 04:15
2 | 2024-09-01 04:05:14.129 | 2024-09-01 04:05
3 | 2024-09-01 07:00:25.354 | 2024-09-01 07:00
4 | 2024-09-01 09:15:01.033 | 2024-09-01 09:15
5 | 2024-09-01 12:56:58.014 | 2024-09-01 12:56
Notice that the new column named no_seconds contains the timestamp from the start_time column without showing the seconds.
Feel free to use similar syntax to remove the seconds from a timestamp column in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
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
