when i get more than "2262-04-12 07:47:16" on datetime64,get wrong value by time.Time

[joneechua]

Observed

1. my table is:
   CREATE TABLE t2 ( idUInt8,dt DateTime64(8, 'Asia/Shanghai') ) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192
2. insert two record
   insert into t2 values(1,'2262-04-12 07:47:18'),(2,'2262-04-12 07:47:16.854750000')
3. read sql rows by go driver,and get the time.Time object, print format string and unix int64
   rows.Scan(var interface{}...) b, _ := val.(time.Time) log.Println(b.Format("2006-01-02 15:04:05.00000000"), b.Unix())

Expected behaviour

get Expected Result
1, 2262-04-12 07:47:18.00000000 2, 2262-04-12 07:47:16.85475000

Code example

package main

import (
        "database/sql"
        "fmt"
        _ "github.com/ClickHouse/clickhouse-go/v2"
        "log"
        "regexp"
        "strconv"
        "strings"
        "time"
)

func searchDb(rows *sql.Rows) error {
        columns, err := rows.Columns()
        if err != nil {
                return err
        }
        columnTypes, err := rows.ColumnTypes()
        if err != nil {
                return err
        }
        for i, ctype := range columnTypes {
                p, s, ok := ctype.DecimalSize()
                if ok {
                        log.Printf("%d name: %s type: %s -> (%d,%d)", i, ctype.Name(), ctype.DatabaseTypeName(), p, s)
                } else {
                        log.Printf("%d name: %s type: %s", i, ctype.Name(), ctype.DatabaseTypeName())
                }
        }
        count := len(columns)

        mData := make([]map[string]interface{}, 0)
        values := make([]interface{}, count)
        valPointers := make([]interface{}, count)
        for rows.Next() {
                for i := 0; i < count; i++ {
                        valPointers[i] = &values[i]
                }

                rows.Scan(valPointers...)
                entry := make(map[string]interface{})

                for i, col := range columns {
                        var v interface{}
                        val := values[i]
                        switch val.(type) {
                        case nil:
                                log.Println(i, "nil", val)
                        case uint8:
                                b, _ := val.(uint8)
                                log.Println(i, "uint8", strconv.FormatInt(int64(b), 10))
                                v = strconv.FormatInt(int64(b), 10)
                        case time.Time:
                                colDef := columnTypes[i]
                                colTypeName := colDef.DatabaseTypeName()
                                b, _ := val.(time.Time)
                                if strings.Contains(colTypeName, "DateTime") {
                                        colFmt := fmt.Sprintf("2006-01-02 15:04:05")
                                        re := regexp.MustCompile(`\((\d+)[,]?`)
                                        matches := re.FindStringSubmatch(colTypeName)
                                        if len(matches) > 1 {
                                                var p int64 = 0
                                                if i, err := strconv.ParseInt(matches[1], 10, 64); err == nil {
                                                        p = i
                                                }
                                                colFmt += ("." + strings.Repeat("0", int(p)))
                                        }
                                        v = b.Format(colFmt)
                                } else if colTypeName == "Date" || colTypeName == "Date32" {
                                        v = b.In(b.Location()).Format("2006-01-02")
                                } else {
                                        v = b
                                }
                                log.Println(i, "time.Time", colTypeName, v, " ->", b.Unix())
                        }
                        entry[col] = v
                }
                mData = append(mData, entry)
        }
        if rows.NextResultSet() {
                return searchDb(rows)
        }
        return nil
}

func main() {
        db, err := sql.Open("clickhouse", dsn)
        if err != nil {
                log.Fatal("Error connecting to ClickHouse:", err)
        }
        defer db.Close()

        rows, err := db.Query(`select * from t2`)
        if err != nil {
                log.Fatal("Error executing query:", err)
        }
        defer rows.Close()

        searchDb(rows)

        if err := rows.Err(); err != nil {
                log.Fatal(err)
        }
}

Error log

get the Unexpected results, and it appears that an overflow value is returned by time.Time.Unix()

2024/05/28 17:17:18 0 name: id type: UInt8
2024/05/28 17:17:18 1 name: dt type: DateTime64(8, 'Asia/Shanghai')
2024/05/28 17:17:18 0 uint8 1
2024/05/28 17:17:18 1 time.Time DateTime64(8, 'Asia/Shanghai') 1677-09-21 08:18:27.29044838  -> -9223372036
2024/05/28 17:17:18 0 uint8 2
2024/05/28 17:17:18 1 time.Time DateTime64(8, 'Asia/Shanghai') 2262-04-12 07:47:16.85475000  -> 9223372036

Details

Environment

• clickhouse-go version: v2.24.0
• Interface: ClickHouse API / database/sql compatible driver
• Go version:1.22.2
• Operating system: macos 14.5(arm64) / CentOS 7.2(amd64)
• ClickHouse version: 23.8.9.1
• Is it a ClickHouse Cloud? tencent cloud
• ClickHouse Server non-default settings, if any: none
• CREATE TABLE statements for tables involved: See No. 1 above
• Sample data for all these tables, use clickhouse-obfuscator if necessary

[jkaflik]

Hello @joneechua

Please provide all details

[joneechua]

Hello @joneechua

Please provide all details

i read the clickhouse result by the go code above，get the unexpected result

2024/05/28 17:17:18 1 time.Time DateTime64(8, 'Asia/Shanghai') 1677-09-21 08:18:27.29044838  -> -9223372036

but i read this record from clickhouse client

:) select * from t2 where id=1;

SELECT *
FROM t2
WHERE id = 1

Query id: e2a696c9-5911-4061-ba0d-ca63d561457c

Connecting to database test at ***as user root.
Connected to ClickHouse server version 23.8.9.

ClickHouse server version is older than ClickHouse client. It may indicate that the server is out of date and can be upgraded.

   ┌─id─┬───────────────────────────dt─┐
1. │  1 │ 2262-04-12 07:47:18.00000000 │
   └────┴──────────────────────────────┘

1 row in set. Elapsed: 0.002 sec.

[joneechua]

Hello @joneechua
Please provide all details

i read the clickhouse result by the go code above，get the unexpected result

2024/05/28 17:17:18 1 time.Time DateTime64(8, 'Asia/Shanghai') 1677-09-21 08:18:27.29044838  -> -9223372036

but i read this record from clickhouse client

:) select * from t2 where id=1;

SELECT *
FROM t2
WHERE id = 1

Query id: e2a696c9-5911-4061-ba0d-ca63d561457c

Connecting to database test at ***:3395 as user root.
Connected to ClickHouse server version 23.8.9.

ClickHouse server version is older than ClickHouse client. It may indicate that the server is out of date and can be upgraded.

   ┌─id─┬───────────────────────────dt─┐
1. │  1 │ 2262-04-12 07:47:18.00000000 │
   └────┴──────────────────────────────┘

1 row in set. Elapsed: 0.002 sec.

how can i get the correct string result by go driver? thx.

[jkaflik]

@joneechua please have a look into a "Details" section of issue content. Please provide a driver version and other missing values. Thank you.

[joneechua]

@joneechua please have a look into a "Details" section of issue content. Please provide a driver version and other missing values. Thank you.

sorry about that, I've revised this section.

[slvrtrn]

The max value for any DateTime64 precision is hardcoded as (UTC timezone):

https://github.com/ClickHouse/clickhouse-go/blob/v2.26.0/lib/column/datetime64.go#L37

maxDateTime64, _ = time.Parse("2006-01-02 15:04:05", "2262-04-11 23:47:16")

However, this should be the case only for DateTime64(9); the max value for DateTime64 with less precision should be 2299-12-31 23:59:59; see the docs:

Supported range of values: [1900-01-01 00:00:00, 2299-12-31 23:59:59.99999999]

Note: The precision of the maximum value is 8. If the maximum precision of 9 digits (nanoseconds) is used, the maximum supported value is 2262-04-11 23:47:16 in UTC.

Currently, if you specify a time.Date object that is later than 2262-04-11 23:47:16 but earlier than 2299-12-31 23:59:59, even if your field is DateTime64(0), there will be a rollover to 1900-01-01 00:00:00 (could it be an int overflow on the driver side?).

[jkaflik]

@slvrtrn

maxDateTime64 you mention is unused and should be removed.

I can confirm there is a int64 overflow happening in this line:
https://github.com/ClickHouse/ch-go/blob/main/proto/datetime64.go#L60

we need to do a better math here.