Hi experts ,

We have a use case where we want to aggregate events of last fifteen minutes but we want to ceil it instead of floor the events. TIME_FLOOR with PT300S can be used for floor but we cannot use CEIL function as we can only ceil it to 1 Minute as Unit.

https://druid.apache.org/docs/latest/misc/math-expr.html

Above link talks about timestamp_ceil but it looks like its talking about CEIL function.

Is there a TIME_CEIL function that can be used?

Thanks in advance

I’m not familiar with any TIME_CEIL function, but you should be able to roll one yourself with TIME_SHIFT (e.g. TIME_SHIFT(TIME_FLOOR(__time, ‘P900S’), ‘P900S’, 1)), or even just interval arithmetic (e.g. TIME_FLOOR(__time, ‘P900S’) + INTERVAL ‘15’ MINUTE).

Thanks Julian. This is really helpful. But i wonder isnt this usecase common ? I am sure there must be ample scenarios where we want to show events happening between certain interval at the end of the interval?Any idea or info on this would be useful.

Hey Vindhya,

I’ve raised a PR to add a TIME_CEIL function: https://github.com/apache/incubator-druid/pull/8027. It should make it into a future version. Until then, the workaround that Julian suggested would almost work, but isn’t quite the same as what TIME_CEIL would do. It differs in behavior when the input is something that is already at the ceiling of a time bucket: TIME_CEIL would leave it alone, but TIME_FLOOR + TIME_SHIFT would increment it by one time bucket. You could work around that for now with a CASE statement.

Thanks for the fix and pointng out about the difference for event already in the ceil time Gian.

I am again not sure if this is the right place to ask . When I tried time-series native query too it's basically does the Floor functionality.Is there a reason behind using Floor instead of Ceil ? Is that a common scenario in the analytics data we show in different domains ?

Floor is more common in my experience, since e.g. floor to day tells you what day a data point occurred on. In that sense it is a more natural choice for timeseries queries. But I suppose it’s nice to have ceil as an option too in case someone wants it.

Note this was added in https://github.com/apache/incubator-druid/pull/8027 and will be part of Druid 0.16.0 (as well as the current Imply’s distribution of Druid)