v.24.8Improvement

Improve DateTime Schema Inference and Parsing in ClickHouse

Official CH changelog text
Improve schema inference of date times. Now DateTime64 used only when date time has fractional part, otherwise regular DateTime is used. Inference of Date/DateTime is more strict now, especially when date_time_input_format='best_effort' to avoid inferring date times from strings in corner cases. #68382 (Kruglov Pavel).
Improved schema inference for date and date-time types, where DateTime64 is used only if the date-time value has a fractional part. The inference for Date and DateTime types is now more strict, especially when date_time_input_format='best_effort' is set, reducing incorrect inferring of date-time from strings in edge cases.

Why it matters

This feature aims to enhance accuracy when automatically detecting date and time types in data ingestion scenarios. By stricter inference rules and conditionally using DateTime64, it prevents incorrect or unintended type assignments, improving data correctness and reliability during schema auto-detection.

How to use it

To benefit from the improved inference, ensure that the setting date_time_input_format is configured to 'best_effort' when reading date and time strings. The system will then more accurately infer whether to use Date, DateTime, or DateTime64 types based on the input values.

Related resources

PreviousNext