v.23.9New Feature

Added toDaysSinceYearZero Function with MySQL Compatibility and Enhanced DateTime Support

Official CH changelog text
Added function toDaysSinceYearZero with alias TO_DAYS (for compatibility with MySQL) which returns the number of days passed since 0001-01-01 (in Proleptic Gregorian Calendar). #54479 (Robert Schulze). Function toDaysSinceYearZero now supports arguments of type DateTime and DateTime64. #54856 (Serge Klochkov).
Added the function toDaysSinceYearZero with an alias TO_DAYS that returns the number of days elapsed since 0001-01-01 according to the Proleptic Gregorian Calendar. This function now supports input arguments of type DateTime and DateTime64.

Why it matters

The addition of toDaysSinceYearZero and its alias TO_DAYS provides ClickHouse users compatibility with MySQL by calculating the total number of days since the beginning of year 1 (0001-01-01). This helps in date arithmetic and comparison tasks where a day count since a fixed historical date is required, supporting richer date/time types including DateTime and DateTime64.

How to use it

Use the function in your SQL queries as follows:

SELECT toDaysSinceYearZero(<date_or_datetime_column>);
-- or equivalently
SELECT TO_DAYS(<date_or_datetime_column>);


This will return an UInt32 representing the number of days passed since 0001-01-01. The function accepts Date, DateTime, and DateTime64 types.

Related resources

PreviousNext