Using time.Time with zero value results in "dateTime overflow" error.

[Skandalik]

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

[malekvictor]

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

[Skandalik]

@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

[genzgd]

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.

[lfthomaz]

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.

[amurchick]

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)

[jkaflik]

@amurchick would you like to submit proposal fix PR?

[amurchick]

@amurchick would you like to submit proposal fix PR?

Ok, I'll do it.

[MetalRex101]

Hey! Can anyone please advice, how can I store Null value to a Nullable(DateTime) column? If I pass *time.Time or a time.Time{}, it gives me a dateTime overflow error. Clickhouse go version - 2.30.0 Clickhouse server version - 24.8.4.13

[MetalRex101]

As a workaround it is possible to use a kind of normalization function with a return type of an interface{} or any, which will return nil, when a value should be nil. Only applicable to Batch.Append method. In that case it will insert null instead of default value.