Source: Date, by ClickHouse docs
We moved a table from Postgres to ClickHouse, and every day before 1970 came back as a year between 2079 and 2149. Our migration check reported 1278 days that did not match. The per-day count used ClickHouse toDate; switching it to toDate32 fixed it.
Our test table had 2000 rows. 1278 of its dates and 521 of its timestamps were before 1970, along with some +infinity and -infinity values.
After the copy, our migration check counts rows per day on both sides and compares them. It reported 1278 days that did not match: in ClickHouse, those rows sat on days in the 2079-2149 range, where Postgres had none.
The per-day count used ClickHouse toDate. The ClickHouse Date type is stored in two bytes as the number of days since 1970-01-01 (unsigned), with a supported range of 1970-01-01 to 2149-06-06. A day before 1970 has no place in that range, so it wrapped around into the future. Our probe: toDate of 1900-01-21 returned 2079-06-27.
Date32 is stored as a signed 32-bit number of days, and negative values are the days before 1970-01-01. With toDate32, 1900-01-21 stayed 1900-01-21. We switched the per-day count to toDate32 and ran the check again: the per-day counts matched and 0 of 64 checksums differed.
| Check | Why |
|---|---|
| Look for dates before 1970 in the source table | Those are the days toDate cannot hold. |
Use toDate32 for per-day grouping or counting when they exist | It keeps days before 1970 instead of wrapping them. |
Sources: the Date and Date32 storage and range facts are from the ClickHouse docs. The table, the 1278 differences, the probe and the 0 of 64 result are from our own migration check run.