Select date type from Clickhouse got wrong value

[seifchen]

github.com/jmoiron/sqlx v1.3.5 table_a has a field named data_date and type is date select min(data_date) from table_a limit 10; want got 2023-01-01 but got :2023-01-01 00:00:00Z

[gurza]

The result you're getting is due to the way Go handles date/time data types. Even though you're only storing a date in your SQL database, when you fetch it into a Go time.Time value, Go includes the time as well.

Print (or return) result out using the "2006-01-02" format, which only includes the year, month, and day.

type Result struct {
    DataDate time.Time `db:"data_date"`
}

var result Result
err := db.Get(&result, "SELECT min(data_date) AS data_date FROM table_a LIMIT 10")
if err != nil {
    log.Fatal(err)
}

fmt.Println(result.DataDate.Format("2006-01-02"))

If you want to permanently remove the time from a time.Time value, you can do so using the time.Date

// ...

dateOnly := time.Date(result.DataDate.Year(), result.DataDate.Month(), result.DataDate.Day(), 0, 0, 0, 0, result.DataDate.Location())

[seifchen]

@gurza I'm use the var result string

var result string
err := db.Get(&result, "SELECT min(data_date) AS data_date FROM table_a LIMIT 1")
if err != nil {
    log. Fatal(err)
}
fmt.Printf("res is:%v",result)

got '2023-01-01 00:00:00Z'. I also create a similar table in mysql and data_date type is date, but got correct value '2023-01-01'

[gurza]

@seifchen you are getting different results because different databases handle date and time types in their own way. MySQL and ClickHouse, in your case, are handling the DATE type differently.

With ClickHouse use toDate() to return just the date part (without the time).

var result string
err := db.Get(&result, "SELECT toDate(min(data_date)) AS data_date FROM table_a LIMIT 1")
// ...

https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#todate

[wac-0b]

@seifchen try to exec the following query in Clickhouse.

SELECT
    min(data_date) AS data_date,
    toTypeName(data_date)
FROM table_a LIMIT 1

You will see that the expression type is DateTime, not Date.

You need to use type conversion either in the database or in the program. @gurza provided examples of both above.

[seifchen]

@wac-0b I run

SELECT
    toTypeName(data_date)
FROM table_a LIMIT 1

got the result is Date. @gurza Why I use the http method to run

SELECT min(data_date) AS data_date FROM table_a LIMIT 1

got the correct value '2023-01-01' the ip:port is the same.

[gurza]

First of all, please execute the query that @wac-0b wrote. Right now you have executed a different query.

toTypeName(min(data_date))

This is needed to make sure that the resulting value type of the min() function will be DateTime. Isn't it?

As for the features of the ClickHouse http-client operation, that is beyond this discussion.

[seifchen]

@gurza I print log in my code like this:

    var res string
    sql := fmt.Sprintf("select toTypeName(min(data_date)) from table_a limit 1;", field, tableName)
    if err := pool.GetContext(newCtx, &res, sql); err != nil {
        return "", fmt.Errorf("select min value with tableName:%s field:%s got:%w", tableName, field, err)
    }
    log.InfoContextf(ctx, "minValue type name is:%+v", res)

and the log print

[seifchen]

@gurza @wac-0b I use

select toString(min(data_date)) from table_a limit 1;

got my supported value

[gurza]

@seifchen it's cool. Please close this issue.