Hi,
I’m trying to execute the following query on a Druid datasource using *Apache
Calcite*:
SELECT
FLOOR(
("__time" + INTERVAL '5' HOUR + INTERVAL '30' MINUTE)
TO DAY
) AS "createtime.day",
COUNT(*) AS "count"
FROM "druid"."event_retracted";
The goal is to adjust the __time column to a specific timezone (UTC+5:30)
and then floor it to the day.
However, when running this query via the Calcite Druid adapter, I get the
following error:
Technical Failure: When you perform arithmetic inside a FLOOR function
(like adding 5.5 hours), the adapter fails to determine a valid
"Origin" (start time) for the flooring operation).
>From my investigation:
-
*Correct Behavior:* The adapter should pass null as the origin, letting
Druid use the default epoch.
-
*Buggy Behavior:* It passes an empty string '' as the origin, which
leads to the failure.
*Question:*
1.
Is there a workaround to perform time arithmetic and FLOOR in Apache
Calcite with Druid without triggering this error?
2.
Are there alternative approaches (functions or raw queries) that can
achieve the same effect of timezone adjustment and daily rounding?
3.
Is this a known bug in the Calcite Druid adapter, and is there a fix in
recent versions?
I’ve seen TIME_FLOOR in native Druid queries handle this correctly, but I
need to achieve the same via Calcite SQL. Any guidance would be appreciated.
The stackoverflow question :-
https://stackoverflow.com/questions/79839015/apache-calcite-druid-adapter-floor-with-time-arithmetic-fails-to-determine-vali
