v.23.9New Feature

Added Date Conversion Functions for Integer Encoded Dates

Official CH changelog text
Added functions YYYYMMDDtoDate, YYYYMMDDtoDate32, YYYYMMDDhhmmssToDateTime and YYYYMMDDhhmmssToDateTime64. They convert a date or date with time encoded as integer (e.g. 20230911) into a native date or date with time. As such, they provide the opposite functionality of existing functions YYYYMMDDToDate, YYYYMMDDToDateTime, YYYYMMDDhhmmddToDateTime, YYYYMMDDhhmmddToDateTime64. #54509 (Quanfa Fu) (Robert Schulze).
Added new functions YYYYMMDDtoDate, YYYYMMDDtoDate32, YYYYMMDDhhmmssToDateTime, and YYYYMMDDhhmmssToDateTime64 that convert integer-encoded dates and timestamps (e.g., 20230911) into native Date, Date32, DateTime, and DateTime64 types respectively.

Why it matters

These functions provide the inverse conversion of existing functions like YYYYMMDDToDate and YYYYMMDDToDateTime, enabling users to parse integer-encoded date/time values into native ClickHouse date/time types. This solves the problem of converting compact numeric representations commonly found in external systems or data formats into ClickHouse's date/time types for easier querying and analysis.

How to use it

Use the new functions to convert integer-based date or datetime values into native ClickHouse types. For example:

SELECT YYYYMMDDtoDate(20230911), YYYYMMDDhhmmssToDateTime(20230911123045);

This will output native Date and DateTime values from integer inputs.

Related resources

PreviousNext