Affected Version
0.14.2
Description
SELECT CEIL(TIMESTAMP '2000-01-01 00:00:00' TO DAY) returns 2000-01-02T00:00:00.000Z, but 2000-01-01T00:00:00.000Z would make more sense.
This happens because what CEIL(<timestamp> TO Y) does is return the (exclusive) upper bound of the time bucket that includes <timestamp>. In particular, because it's exclusive, it will never be equal to the timestamp. If you pass in something that is at the bottom end of a bucket then you'll get the start of the next bucket.
The function doesn't seem to be standardized (at least I can't find docs on it as a standard SQL function anywhere) so I guess it can behave however we want. IMO the current behavior is a bit weird, since it's non-idempotent. So it would make more sense to special case it so it returns the provided timestamp if that timestamp is already the upper bound of a time bucket.
Original discussion: https://groups.google.com/d/topic/druid-user/199ADDTT08Q/discussion
Affected Version
0.14.2
Description
SELECT CEIL(TIMESTAMP '2000-01-01 00:00:00' TO DAY)returns2000-01-02T00:00:00.000Z, but2000-01-01T00:00:00.000Zwould make more sense.This happens because what
CEIL(<timestamp> TO Y)does is return the (exclusive) upper bound of the time bucket that includes<timestamp>. In particular, because it's exclusive, it will never be equal to the timestamp. If you pass in something that is at the bottom end of a bucket then you'll get the start of the next bucket.The function doesn't seem to be standardized (at least I can't find docs on it as a standard SQL function anywhere) so I guess it can behave however we want. IMO the current behavior is a bit weird, since it's non-idempotent. So it would make more sense to special case it so it returns the provided timestamp if that timestamp is already the upper bound of a time bucket.
Original discussion: https://groups.google.com/d/topic/druid-user/199ADDTT08Q/discussion