Time rounding to different intervals

[Saptarshi Gan]

Like toStartOfMinute toStartOfHour is it possible to round up the date time to any random interval? Like to Start of 2 minute or to Start of 10 minute!

[Saptarshi Gan]

Is there a way to convert datetime to unix timestamp?

[Saptarshi Gan]

Multiple CASTS would do the trick I guess!

CAST(floor(CAST(timestamp AS UInt64)/300)*300 AS DateTime)

[Alex Zatelepin]

You can use functions toUnixTimestamp and intDiv for this task - that will be slightly more efficient. Your query becomes:

toDateTime(intDiv(toUnixTimestamp(timestamp), 300) * 300)

(BTW there is a function toStartOfFiveMinute specifically for 300 seconds)