Incorrect UTC time formatting in formatTime

[Ignatella]

Observed

When writing session timezone is other than UTC ("session_timezone": timezone setting) inserting to DateTime column value:

now := time.Now().UTC() // 2025-11-12 16:33:21

leads to

timezone:   America/New_York
date: 2025-11-12 21:33:21

timezone:   Europe/Warsaw
date: 2025-11-12 15:33:21

timezone:  UTC
date: 2025-11-12 16:33:21

The example falls into:

clickhouse-go/bind.go

Line 252 in df28730

return value.Format("toDateTime('2006-01-02 15:04:05')"), nil

I wonder if handling all dates can be done just like in this case (.Unix...()):

clickhouse-go/bind.go

Line 233 in df28730

case "Local", "":

Effectively simplifying the conversion and dealing with all cases by golang side.

Code example

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/ClickHouse/clickhouse-go/v2"
	"github.com/ClickHouse/clickhouse-go/v2/lib/driver"
)

const (
	clickhouseAddr = "x.x.x.x:9000"
	database       = "default"
	username       = "default"
	password       = ""
)

func main() {
	now := time.Now().UTC()

	fmt.Printf("Current UTC time: %s\n\n", now.Format("2006-01-02 15:04:05"))

	// Timezone to tests
	timezones := []string{
		"UTC",
		"Europe/Warsaw",
		"America/New_York",
	}

	// Create table
	fmt.Println("Creating table...")
	connUTC := connectClickHouse("UTC")
	defer connUTC.Close()

	if err := createTable(connUTC); err != nil {
		log.Fatal("Failed to create table:", err)
	}
	fmt.Println("Table created successfully")

	fmt.Println("Inserting data with different timezone connections...")
	for _, tz := range timezones {
		conn := connectClickHouse(tz)

		if err := insertData(conn, tz, now); err != nil {
			log.Printf("Failed to insert data for timezone %s: %v", tz, err)
		} else {
			fmt.Printf("Inserted data with timezone: %s\n", tz)
		}

		_ = conn.Close()
	}

	fmt.Println("Querying stored data:")

	if err := queryData(connUTC); err != nil {
		log.Fatal("Failed to query data:", err)
	}
}

func connectClickHouse(timezone string) driver.Conn {
	conn, err := clickhouse.Open(&clickhouse.Options{
		Addr: []string{clickhouseAddr},
		Auth: clickhouse.Auth{
			Database: database,
			Username: username,
			Password: password,
		},
		Settings: clickhouse.Settings{
			"session_timezone": timezone,
		},
		Debug: false,
	})

	if err != nil {
		log.Fatalf("Failed to connect to ClickHouse with timezone %s: %v", timezone, err)
	}

	if err := conn.Ping(context.Background()); err != nil {
		log.Fatalf("Failed to ping ClickHouse with timezone %s: %v", timezone, err)
	}

	return conn
}

func createTable(conn driver.Conn) error {
	ctx := context.Background()

	if err := conn.Exec(ctx, "DROP TABLE IF EXISTS default.test"); err != nil {
		return fmt.Errorf("failed to drop table: %w", err)
	}

	query := `
		CREATE TABLE default.test
		(
			id String,
			date DateTime('UTC')
		)
		ENGINE = MergeTree
		ORDER BY (id, date)
	`

	return conn.Exec(ctx, query)
}

func insertData(conn driver.Conn, id string, date time.Time) error {
	ctx := context.Background()

	query := "INSERT INTO default.test (id, date) VALUES (?, ?)"

	return conn.Exec(ctx, query, id, date)
}

func queryData(conn driver.Conn) error {
	ctx := context.Background()

	rows, err := conn.Query(ctx, "SELECT id, toString(date) as date_string FROM default.test ORDER BY id")
	if err != nil {
		return err
	}
	defer rows.Close()

	fmt.Printf("%-20s | %-20s\n", "ID (Timezone)", "Date as String")
	fmt.Println("--------------------------------------------------------------------------")

	for rows.Next() {
		var (
			id         string
			dateString string
		)

		if err := rows.Scan(&id, &dateString); err != nil {
			return err
		}

		fmt.Printf("%-20s | %-20s\n", id, dateString)
	}

	return rows.Err()
}

Details

Environment

• clickhouse-go version: v2.40.3
• Interface: ClickHouse API / database/sql compatible driver
• Go version: 1.25.2
• Operating system: macOS
• ClickHouse version: 24.8.4.13
• Is it a ClickHouse Cloud? no
• ClickHouse Server non-default settings, if any:
• CREATE TABLE statements for tables involved:
• Sample data for all these tables, use clickhouse-obfuscator if necessary

[Ignatella]

related
#759
#719