[SOLVED] How to perform hourly aggregation in time-series table?

How to perform hourly aggregation in time-series table?

I am using GridDB Cloud for time-series analysis and need to calculate hourly average temperature readings from a table containing sensor data. I want to group my readings into 1-hour intervals and compute the average temperature for each interval, but I am not sure which SQL function or method is supported for this in GridDB Cloud.

Table schema:

CREATE TABLE SensorData (
    timestamp TIMESTAMP,
    temp DOUBLE
);

Sample data:

INSERT INTO SensorData (timestamp, temp) VALUES
(TIMESTAMP('2025-08-22T01:05:00Z'), 20.5),
(TIMESTAMP('2025-08-22T01:25:00Z'), 21.0),
(TIMESTAMP('2025-08-22T01:45:00Z'), 22.0),
(TIMESTAMP('2025-08-22T03:10:00Z'), 21.2),
(TIMESTAMP('2025-08-22T03:55:00Z'), 22.4),
(TIMESTAMP('2025-08-22T05:15:00Z'), 22.1);

Expected result:

2025-08-22 01:00:00 → 21.17
2025-08-22 03:00:00 → 21.8
2025-08-22 05:00:00 → 22.1

I want to group this data by 1-hour intervals and calculate the average temperature for each.

I looked for time-based aggregation functions in the GridDB documentation and online resources, but I couldn’t find a clear example of how to perform hourly grouping and averaging.

Question:
How can I group this data into 1-hour intervals and calculate the average temperature for each in GridDB Cloud?

Solution

In GridDB, you can use column aliases in the GROUP BY clause.

SELECT
  TIMESTAMP_TRUNC(HOUR, timestamp) AS ts_to_hour,
  AVG(temp) AS temp
FROM SensorData
GROUP BY ts_to_hour
ORDER BY ts_to_hour;

Result.

+--------------------------+-------+
| ts_to_hour               | temp  |
+--------------------------+-------+
| 2025-08-22T01:00:00.000Z | 21.17 |
| 2025-08-22T03:00:00.000Z | 21.8  |
| 2025-08-22T05:00:00.000Z | 22.1  |
+--------------------------+-------+