Group by Time

To group by time in SQL, you can use the DATE_TRUNC function (or equivalent functions(s) for your platform, see below) to truncate the date/time column to the desired granularity, such as hour or minute. Then, you can use the GROUP BY clause to group the results by the truncated column.

For example, consider the following sales table:

To group the transaction_date column by hour, you could use the following query:

SELECT DATE_TRUNC('hour', transaction_date) as hour, SUM(amount) as total_sales 
FROM sales GROUP BY hour;

This query selects the truncated transaction_date column as hour and the sum of the amount as total_sales. Then, it groups the results by the hour column.

The DATE_TRUNC accepts two parameters: the first parameter is the desired truncation unit (such as hour, minute, or day), and the second parameter is the column to truncate. You can adjust the first parameter to group by a different granularity, such as minute or second.

Here’s the result set showing the total_sales per hour:

Note that the exact syntax and supported date/time functions may vary depending on the specific SQL dialect used in your database.

Database-Specific Functions for Truncating Timestamps

To make it easy for you to implement time-based grouping across various databases, here’s a table that outlines the equivalent functions you can use:

Database Platform	Equivalent Timestamp Truncation Function(s)
Redshift	DATE_TRUNC
Snowflake	DATE_PART and TO_VARIANT
BigQuery	TIMESTAMP_TRUNC
Netezza	DATE_TRUNC
Db2	TRUNC or TRUNCATE
Hive	TRUNC or date_format
Postgres	DATE_TRUNC
SQL Server	FORMAT with CAST or CONVERT

 

Here’s a quick overview of how you can use these functions in the above database plaforms:

Redshift

SELECT DATE_TRUNC('hour', transaction_date) AS hour, SUM(amount) AS total_sales
FROM sales
GROUP BY DATE_TRUNC('hour', transaction_date);

Snowflake

SELECT DATE_PART('hour', transaction_date) AS hour, SUM(amount) AS total_sales
FROM sales
GROUP BY DATE_PART('hour', transaction_date);

BigQuery

SELECT TIMESTAMP_TRUNC(transaction_date, HOUR) AS hour, SUM(amount) AS total_sales
FROM sales
GROUP BY TIMESTAMP_TRUNC(transaction_date, HOUR);

Netezza

SELECT DATE_TRUNC('hour', transaction_date) AS hour, SUM(amount) AS total_sales
FROM sales
GROUP BY DATE_TRUNC('hour', transaction_date);

Db2

SELECT TRUNC(transaction_date, 'HH24') AS hour, SUM(amount) AS total_sales
FROM sales
GROUP BY TRUNC(transaction_date, 'HH24');

Hive

SELECT date_format(transaction_date, 'yyyy-MM-dd HH') AS hour, SUM(amount) AS total_sales
FROM sales
GROUP BY date_format(transaction_date, 'yyyy-MM-dd HH');

Postgres

SELECT DATE_TRUNC('hour', transaction_date) AS hour, SUM(amount) AS total_sales
FROM sales
GROUP BY DATE_TRUNC('hour', transaction_date);

SQL Server

SELECT FORMAT(transaction_date, 'yyyy-MM-dd HH:00:00') AS hour, SUM(amount) AS total_sales
FROM sales
GROUP BY FORMAT(transaction_date, 'yyyy-MM-dd HH:00:00');