Often in PostgreSQL you may want to truncate timestamp values to 5-minute intervals.
You can use the date_bin() function with the following syntax to do so:
SELECT *,
date_bin('5 min', start_time, '2000-1-1') AS five_min
FROM emp;
This particular example truncates the timestamps in the start_time column of the table named emp to five-minute intervals and saves the results to a column named near_min.
Note that we used the date_bin() function to achieve this result. This function uses the following syntax:
date_bin(interval, timestamp, origin)
where:
- interval: The interval to truncate to
- timestamp: The name of the timestamp column
- origin: An “origin” of matching datatype to avoid unexpected results from incorrect timezone assumptions
The following example shows how to use this syntax in practice.
Example: How to Truncate Timestamp to 5-Minute Intervals 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:17:43');
INSERT INTO emp VALUES (0002, '2024-04-01 04:36:56');
INSERT INTO emp VALUES (0003, '2024-10-26 07:09: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:17:43
2 | 2024-04-01 04:36:56
3 | 2024-10-26 07:09:10
4 | 2024-12-14 09:15:00
5 | 2024-12-07 12:56:30
Suppose that we would like to truncate each timestamp in the start_time column to a 5-minute interval.
We can use the following syntax to do so:
SELECT *,
date_bin('5 min', start_time, '2000-1-1') AS five_min
FROM emp;
empid | start_time | five_min
-------+---------------------+---------------------
1 | 2024-09-23 04:17:43 | 2024-09-23 04:15:00
2 | 2024-04-01 04:36:56 | 2024-04-01 04:35:00
3 | 2024-10-26 07:09:10 | 2024-10-26 07:05: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:55:00
The resulting table contains a new column named five_min that contains the timestamp from the start_time column truncated to a 5-minute interval.
Feel free to use similar syntax to truncate a timestamp column to a 5-minute interval 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
