ClickHouse
when i get more than "2262-04-12 07:47:16" on datetime64,get wrong value by time.Time
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-goversion: v2.24.0 - [ ] Interface: ClickHouse API /
database/sqlcompatible 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 TABLEstatements for tables involved: See No. 1 above - [ ] Sample data for all these tables, use clickhouse-obfuscator if necessary
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.
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 -> -9223372036but 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.
@joneechua please have a look into a "Details" section of issue content. Please provide a driver version and other missing values. Thank you.
@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.
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?).
@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.