clickhouse-go
clickhouse-go copied to clipboard
ClickHouse
Using time.Time with zero value results in "dateTime overflow" error.
Issue description
We're using jmoiron/sqlx library to insert data to CH database.
When I switched project to use clickhouse-go/v2, inserting object with field time.Time with zero value (0001-01-01 00:00:00 +0000 UTC) results in dateTime overflow error.
In previous v1, it was inserting the beginning of Unix timestamp.
Example code
type Event struct {
EventAt time.Time `db:"event_at"` // this one is filled with correct data
DifferentEventAt time.Time `db:"different_event_at"` // this one is untouched, so zero time.Time value remains
}
Error log
clickhouse: dateTime overflow. different_event_at must be between 1970-01-01 00:00:00 and 2105-12-31 23:59:59
Configuration
OS: macOS M1
Interface:
golang.yandex/hasql/sqlx along with github.com/jmoiron/sqlx
Driver version: v2.5.1
Go version: 1.19.4
ClickHouse Server version: 21.5.7.9 running on Docker
I also caught this problem
After reading the source code, I found the solution for myself. I just wrap my model in sql.NullTime before inserting.
toInsertTime := sql.NullTime {
Time: myTime,
Valid: !myTime.IsZero(),
}
How the driver parses the time found here
But time will be 1970-01-01 00:00:00 because clickhouse defaults zero value
Value 0000-00-00 00:00:00 changes to 1970-01-01 00:00:00 since v20.7
@malekvictor as a workaround I'm using int64 and passing myTime.Unix(), and for fields that don't have this field set, it's just 0, so it will set 1970-01-01 00:00:00 in CH
I could see handling this to check specifically for the GoLang 0 value (and switching it to a ClickHouse 1970-01-01 zero value), but otherwise leaving the overflow logic in place. We don't want to silently switch other values that ClickHouse can't handle to 1970-01-01.
I am facing the same issue when doing a dump/restore of some tables using clickhouse-go, I can't reinsert in a database zero date values extracted with the Query method. Converting all the zero dates to "1970-01-01" is not really a good solution. I'd expect clickhouse-go to automatically convert Go zero dates to the minimum CH value.
Have same issue.
As solution - change this: https://github.com/ClickHouse/clickhouse-go/blob/364719b68f57de0f52ea0c99fe3a5e58217bf5d6/lib/column/datetime64.go#L216
case time.Time:
if err := dateOverflow(minDateTime64, maxDateTime64, v, "2006-01-02 15:04:05"); err != nil {
return err
}
col.col.Append(v)
to this (same as for case when type of value is *date.Time and value nil):
case time.Time:
if !v.IsZero() {
if err := dateOverflow(minDateTime64, maxDateTime64, v, "2006-01-02 15:04:05"); err != nil {
return err
}
}
col.col.Append(v)
