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

joneechua commented 1 month ago

Observed

my table is: CREATE TABLE t2 ( idUInt8, dtDateTime64(8, 'Asia/Shanghai') ) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192
insert two record insert into t2 values(1,'2262-04-12 07:47:18'),(2,'2262-04-12 07:47:16.854750000')
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 commented 1 month ago

Hello @joneechua

Please provide all details

joneechua commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

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

joneechua commented 1 month ago

@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.

ClickHouse / clickhouse-go