v.22.4New Feature

INTERVAL enhancement with new time functions in ClickHouse

Official CH changelog text
INTERVAL improvement - can be used with [MILLI|MICRO|NANO]SECOND. Added toStartOf[Milli|Micro|Nano]second() functions. Added [add|subtract][Milli|Micro|Nano]seconds(). #34353 (Andrey Zvonov).
Enhanced INTERVAL support in ClickHouse to include MILLISECOND, MICROSECOND, and NANOSECOND units. Added new functions toStartOfMillisecond(), toStartOfMicrosecond(), toStartOfNanosecond() and arithmetic functions addMilliseconds(), subtractMilliseconds(), addMicroseconds(), subtractMicroseconds(), addNanoseconds(), subtractNanoseconds().

Why it matters

This feature enables finer-grained time interval handling in ClickHouse by allowing users to work with sub-second precision intervals. It improves time-series data manipulation capabilities, allowing precise truncation and arithmetic operations at millisecond, microsecond, and nanosecond levels, thus increasing flexibility and accuracy in time-based queries.

How to use it

Use the new INTERVAL units MILLISECOND, MICROSECOND, and NANOSECOND in time interval expressions. Call the new functions like toStartOfMillisecond(), toStartOfMicrosecond(), or toStartOfNanosecond() to truncate timestamps. Use arithmetic functions such as addMilliseconds(), subtractMicroseconds(), and addNanoseconds() to perform precise time additions or subtractions.

Related resources

PreviousNext