5 ways to parse Dates and DateTimes in ClickHouse

Summary

ClickHouse provides several ways to convert dates and datetimes from Unix timestamps, packed numeric values, known-format strings, mixed inputs, and file data. The examples use fromUnixTimestamp and its millisecond and microsecond variants, YYYYMMDDToDate and YYYYMMDDhhmmssToDateTime, plus parseDateTime or parseDateTimeInJodaSyntax when the input format is known. For unknown or mixed formats, parseDateTimeBestEffort and its DateTime64 counterpart accept inputs such as ISO 8601 strings, dates, and timestamp strings; invalid values otherwise raise an exception, while OrNull returns NULL and OrZero returns January 1, 1970 at midnight. The post also shows direct ::DateTime casting, controlled by cast_string_to_date_time_mode: basic supports standard forms, whereas best_effort broadens parsing but still throws for completely invalid dates. An inline or session setting lets the same casting approach parse rows from a CSV file.

Takeaways

1. 1

   fromUnixTimestamp returns DateTime, while fromUnixTimestamp64Milli and fromUnixTimestamp64Micro return DateTime64(3) and DateTime64(6), respectively.

2. 2

   parseDateTimeBestEffort64 throws on completely invalid input; its OrNull variant returns NULL, and its OrZero variant returns January 1, 1970 at midnight.

3. 3

   cast_string_to_date_time_mode defaults to basic; setting it to best_effort enables broader formats, but completely invalid dates still cause an exception.