[SOLVED] How to group by day in GridDB Cloud without manually concatenating year, month, and day?

How to group by day in GridDB Cloud without manually concatenating year, month, and day?

Table schema:

CREATE TABLE WeatherReadings 
(
    ts TIMESTAMP,
    temp DOUBLE
);

Sample data:

INSERT INTO WeatherReadings (ts, temp) 
VALUES
(TIMESTAMP('2025-08-22T01:05:00Z'), 20.5),
(TIMESTAMP('2025-08-22T05:25:00Z'), 21.8),
(TIMESTAMP('2025-08-22T12:15:00Z'), 29.2),
(TIMESTAMP('2025-08-23T03:10:00Z'), 19.5);

What I tried: I attempted to use EXTRACT on the timestamp column for grouping:

SELECT 
    EXTRACT(YEAR, ts), 
    EXTRACT(MONTH, ts), 
    EXTRACT(DAY, ts), 
    MIN(temp), 
    MAX(temp)
FROM 
    WeatherReadings
GROUP BY 
    EXTRACT(YEAR, ts), 
    EXTRACT(MONTH, ts), 
    EXTRACT(DAY, ts);

But this query fails with an error:

The invalid query was specified

I expected to get min/max temperatures grouped by each calendar day:

2025-08-22 → Min=20.5, Max=29.2
2025-08-23 → Min=19.5, Max=19.5

Documentation reference:

The GridDB docs for EXTRACT show examples with literal timestamps (e.g. EXTRACT(YEAR, TIMESTAMP('2018-12-01T10:30:02.392Z'))), but do not explain whether EXTRACT can be used with columns in GROUP BY.

Question

Is there a way in GridDB Cloud to group directly by date (day precision) without having to manually concatenate EXTRACT(YEAR), EXTRACT(MONTH), and EXTRACT(DAY) into a string? Or is this not supported?

Solution

Using TIMESTAMP_TRUNC:

SELECT TIMESTAMP_TRUNC(DAY, ts), MIN(temp), MAX(temp)
FROM WeatherReadings
GROUP BY TIMESTAMP_TRUNC(DAY, ts);

Alternatively using subquery:

SELECT s.day_trunc, MIN(s.temp), MAX(s.temp) 
FROM (SELECT TIMESTAMP_TRUNC(DAY, ts) AS day_trunc, temp 
      FROM WeatherReadings) s 
GROUP BY s.day_trunc;