Group by time() with odd intervals and preset time boundaries


The documentation for the group by time clause makes many references to “preset time boundaries”, but nowhere are these preset boundaries defined. What are these exactly?

I’m getting very strange timestamps returned for what should be a very simple where and group by time clause:
WHERE (time >= 1527120000000ms AND time <= 1527638400000ms) GROUP BY time(27m)

That’s a start time of Thursday, May 24, 2018 12:00:00 AM.

The first timestamp returned by this query is:
1527118920000 (Wednesday, May 23, 2018 11:42:00 PM)

How is this possible? Shouldn’t the first timestamp of any group by time clause be midnight UTC and then the next interval be the group by time (in this case it should be 12:27:00). This is what is suggested by the group by time examples in the documentation. I can’t figure out why it doesn’t work the same way here.


I would expect GROUP BY time(27m) to align on the Unix epoch. Is that what is happening? Since intervals of 27m do not coincide with every midnight UTC boundary, you won’t align with midnight UTC except as the multiples of 27m coincide with multiples of 24h, which is to say not very often.