Using Group by hour in SAP HANA table

When working with SAP HANA tables, grouping data by hour is a common requirement for time-based analysis. This can be achieved using different approaches depending on your specific needs.

Method 1: Using DATE() and HOUR() Functions

You can try this method to convert time to date and hour format −

select to_varchar(time, 'YYYY-MM-DD'), hour(time),
sum(r_time) as r_time, sum(p_time) as p_time from t1
group by date(time), hour(time)
order by to_varchar(time, 'YYYY-MM-DD'), hour(time);

This approach extracts the date and hour components separately from the timestamp column. The DATE() function extracts the date part, while HOUR() extracts the hour component (0-23). This method gives you separate columns for date and hour, making it useful when you need granular control over the grouping.

Method 2: Using SERIES_ROUND() Function

You can also try using SERIES_ROUND() with a group by clause −

select SERIES_ROUND(time, 'INTERVAL 1 HOUR') as time,
sum(r_time) as r_time, sum(p_time) as p_time from t1
group by SERIES_ROUND(time, 'INTERVAL 1 HOUR')
order by SERIES_ROUND(time, 'INTERVAL 1 HOUR');

The SERIES_ROUND() function rounds the timestamp to the nearest hour boundary, effectively grouping all records within the same hour together. This method returns a single timestamp column rounded to the hour, which is cleaner when you want to maintain the timestamp format in your results.

Both methods effectively group your data by hour intervals, allowing you to perform aggregate operations like SUM() on your numeric columns for hourly analysis.