Often in PostgreSQL you may want to group the results of a query based on a specific time interval.
You can use the date_bin() function with the following syntax to do so:
SELECT date_bin('5 min', sale_time, '2000-1-1') AS five_min,
SUM(amount) AS total
FROM products
GROUP BY five_min
ORDER BY five_min;
This particular example will calculate the sum of values in the amount column and group the results by 5-minute intervals in the sale_time column.
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 Group Results by Time Interval in PostgreSQL
Suppose that we create the following table named products that contains information about the number of products sold at various times at some company:
-- create table named 'products'
CREATE TABLE products (
productID INTEGER PRIMARY KEY,
sale_time TIMESTAMP,
amount INTEGER
);
-- add rows to the table
INSERT INTO products VALUES (0001, '2024-09-12 04:15:23', 33);
INSERT INTO products VALUES (0002, '2024-09-12 04:17:00', 15);
INSERT INTO products VALUES (0003, '2024-09-12 04:19:21', 20);
INSERT INTO products VALUES (0004, '2024-09-12 04:23:13', 45);
INSERT INTO products VALUES (0005, '2024-09-12 04:24:45', 50);
INSERT INTO products VALUES (0006, '2024-09-12 04:26:23', 23);
INSERT INTO products VALUES (0007, '2024-09-12 04:26:53', 30);
INSERT INTO products VALUES (0008, '2024-09-12 04:28:00', 12);
-- view all rows in table
SELECT * FROM products;
productid | sale_time | amount
-----------+---------------------+--------
1 | 2024-09-12 04:15:23 | 33
2 | 2024-09-12 04:17:00 | 15
3 | 2024-09-12 04:19:21 | 20
4 | 2024-09-12 04:23:13 | 45
5 | 2024-09-12 04:24:45 | 50
6 | 2024-09-12 04:26:23 | 23
7 | 2024-09-12 04:26:53 | 30
8 | 2024-09-12 04:28:00 | 12
Suppose that we would like to calculate the sum of values in the amount column, grouped by 5-minute intervals in the sale_time column.
We can use the following syntax to do so:
SELECT date_bin('5 min', sale_time, '2000-1-1') AS five_min,
SUM(amount) AS total
FROM products
GROUP BY five_min
ORDER BY five_min;
five_min | total
---------------------+-------
2024-09-12 04:15:00 | 68
2024-09-12 04:20:00 | 95
2024-09-12 04:25:00 | 65
The resulting table shows the sum of values from the amount column in the original table, grouped by 5-minute intervals in the sale_time column.
For example, from the output we can see:
- A total of 68 sales were made during the 5-minute interval starting on 2024-09-12 04:15:00.
- A total of 95 sales were made during the 5-minute interval starting on 2024-09-12 04:20:00.
- A total of 65 sales were made during the 5-minute interval starting on 2024-09-12 04:25:00.
Feel free to use similar syntax to group the results of your own query by a specific time interval.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Calculate Difference Between Rows
PostgreSQL: How to Extract Hours and Minutes from Timestamp
