v.22.9Improvement

Add enable_extended_results_for_datetime_functions for Date32 Results in ClickHouse

Official CH changelog text
Add enable_extended_results_for_datetime_functions option to return results of type Date32 for functions toStartOfYear, toStartOfISOYear, toStartOfQuarter, toStartOfMonth, toStartOfWeek, toMonday and toLastDayOfMonth when argument is Date32 or DateTime64, otherwise results of Date type are returned. For compatibility reasons default value is ‘0’. #41214 (Roman Vasin).
Added the enable_extended_results_for_datetime_functions option to allow certain date/time functions to return results as Date32 when their argument is Date32 or DateTime64, enhancing type precision.

Why it matters

This feature addresses the limitation where functions like toStartOfYear, toStartOfISOYear, toStartOfQuarter, toStartOfMonth, toStartOfWeek, toMonday, and toLastDayOfMonth returned results of Date type by default, which provided less precise date representation. By enabling this option, users receive results of type Date32 for those functions when the input argument is Date32 or DateTime64, improving consistency and precision of date handling.

How to use it

To enable this feature, set the enable_extended_results_for_datetime_functions setting to 1. When enabled, the mentioned date/time functions will return results in Date32 type if the argument is Date32 or DateTime64; otherwise, the default Date type result is returned. The default value of the setting is 0 for backward compatibility.

Related resources

PreviousNext