Often in PostgreSQL you may want to round a timestamp to the nearest minute.
You can use the following basic syntax to do so:
SELECT date_trunc('minute', start_time + interval '30 second') AS near_min
FROM emp;
This particular example rounds the timestamp in the start_time column of the table named emp to the nearest minute and saves the results to a column named near_min.
Note: We use the interval command to artificially add 30 seconds to each timestamp. Without doing this, the date_trunc function would simply round each timestamp down to the nearest minute.
The following example shows how to use this syntax in practice.
Example: How to Round Timestamp to Nearest Minute 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 round each timestamp in the start_time column to the nearest minute.
We can use the following syntax to do so:
SELECT *,
date_trunc('minute', start_time + interval '30 second') AS near_min
FROM emp;
empid | start_time | near_min
-------+---------------------+---------------------
1 | 2024-09-23 04:15:23 | 2024-09-23 04:15:00
2 | 2024-04-01 04:05:56 | 2024-04-01 04:06:00
3 | 2024-10-26 07:00:10 | 2024-10-26 07:00:00
4 | 2024-12-14 09:15:00 | 2024-12-14 09:15:00
5 | 2024-12-07 12:56:30 | 2024-12-07 12:57:00
The resulting table contains a new column named near_min that contains the timestamp from the start_time column rounded to the nearest minute.
Feel free to use similar syntax to round a timestamp in a column of your own table to the nearest minute.
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
