Inserting timestamps loses precision down to second

[txomon]

Observed

1. Created a table with DateTime64(6) type
2. Inserted timestamps with nanosecond precision using InsertAsync()
3. Queries returned timestamps with second precision

Expected behaviour

When inserting timestamps, using Insert into testtable values (?,?) format the timestamps lose precision.

This fact is not documented nor explicit, causing likely bugs.

Code example

import (
	"context"
	"testing"
	"time"

	"github.com/ClickHouse/clickhouse-go/v2"
	"github.com/stretchr/testify/require"
)

func TestAsyncInsertQuestionmarkLosesPrecision(t *testing.T) {
	t.Parallel()
	ctx := context.Background()

	// Parse DSN and create connection
	opt, err := clickhouse.ParseDSN("clickhouse://user:password@clickhouse:9000/default?secure=false&skip_verify=true")
	require.NoError(t, err)

	conn, err := clickhouse.Open(opt)
	require.NoError(t, err)
	defer conn.Close()

	// Create test table
	err = conn.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS test_async_insert_questionmark_loses_precision (
			id Int32,
			created_at DateTime64(6),
		) ENGINE = MergeTree()
		ORDER BY id
	`)
	require.NoError(t, err)

	// Clean up table after test
	defer func() {
		_ = conn.Exec(ctx, "DROP TABLE IF EXISTS test_async_insert_questionmark_loses_precision")
	}()

	// Insert data with multiple values
	now := time.Now().UTC().Truncate(time.Microsecond)
	err = conn.AsyncInsert(ctx, `
		INSERT INTO test_async_insert_questionmark_loses_precision (id, created_at)
		VALUES (?, ?)
	`, true, int32(1), now)
	require.NoError(t, err)

	// Query the data back
	rows, err := conn.Query(ctx, "SELECT id, created_at FROM test_async_insert_questionmark_loses_precision WHERE id = 1")
	require.NoError(t, err)
	defer rows.Close()

	// Read the results
	var (
		id        int32
		createdAt time.Time
	)

	require.True(t, rows.Next())
	err = rows.Scan(&id, &createdAt)
	require.NoError(t, err)

	// Verify the data matches what we inserted
	require.Equal(t, int32(1), id)
	require.Equal(t, now, createdAt)
}

Error log

Expected :time.Date(2025, time.April, 21, 11, 43, 4, 352652000, time.UTC)
Actual   :time.Date(2025, time.April, 21, 11, 43, 4, 0, time.UTC)

Details

Environment

• [x] clickhouse-go version: master
• [x] Interface: ClickHouse API
• [x] Go version: 1.24
• [x] Operating system: linux
• [x] ClickHouse version: doesn't matter
• [x] Is it a ClickHouse Cloud? no
• [x] ClickHouse Server non-default settings, if any: none
• [x] CREATE TABLE statements for tables involved: provided in the testcase
• [x] Sample data for all these tables, use clickhouse-obfuscator if necessary

[txomon]

For completeness, I have tried to see if there was the same issue when using parameters or batch insert but both were good:

func TestAsyncInsertPlaceholderLosesPrecision(t *testing.T) {
	t.Parallel()
	ctx := context.Background()

	// Parse DSN and create connection
	opt, err := clickhouse.ParseDSN("clickhouse://user:password@clickhouse:9000/default?secure=false&skip_verify=true")
	require.NoError(t, err)

	conn, err := clickhouse.Open(opt)
	require.NoError(t, err)
	defer conn.Close()

	// Create test table
	err = conn.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS test_async_insert_placeholder_loses_precision (
			id Int32,
			created_at DateTime64(6),
		) ENGINE = MergeTree()
		ORDER BY id
	`)
	require.NoError(t, err)

	// Clean up table after test
	defer func() {
		_ = conn.Exec(ctx, "DROP TABLE IF EXISTS test_async_insert_placeholder_loses_precision")
	}()

	// Insert data with multiple values
	now := time.Now().UTC().Truncate(time.Microsecond)
	ctx = clickhouse.Context(ctx, clickhouse.WithParameters(clickhouse.Parameters{
		"id":         "1",
		"created_at": now.Format("2006-01-02 15:04:05.999999"),
	}))
	err = conn.AsyncInsert(ctx, `
		INSERT INTO test_async_insert_placeholder_loses_precision (id, created_at)
		VALUES ({id: Int32}, {created_at: DateTime64(6)})
	`, true)
	require.NoError(t, err)

	// Query the data back
	rows, err := conn.Query(ctx, "SELECT id, created_at FROM test_async_insert_placeholder_loses_precision WHERE id = 1")
	require.NoError(t, err)
	defer rows.Close()

	// Read the results
	var (
		id        int32
		createdAt time.Time
	)

	require.True(t, rows.Next())
	err = rows.Scan(&id, &createdAt)
	require.NoError(t, err)

	// Verify the data matches what we inserted
	require.Equal(t, int32(1), id)
	require.Equal(t, now, createdAt)
}

func TestBatchInsertLosesPrecision(t *testing.T) {
	t.Parallel()
	ctx := context.Background()

	// Parse DSN and create connection
	opt, err := clickhouse.ParseDSN("clickhouse://user:password@clickhouse:9000/default?secure=false&skip_verify=true")
	require.NoError(t, err)

	conn, err := clickhouse.Open(opt)
	require.NoError(t, err)
	defer conn.Close()

	// Create test table
	err = conn.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS test_batch_insert_loses_precision (
			id Int32,
			created_at DateTime64(6)
		) ENGINE = MergeTree()
		ORDER BY id
	`)
	require.NoError(t, err)

	// Clean up table after test
	defer func() {
		_ = conn.Exec(ctx, "DROP TABLE IF EXISTS test_batch_insert_loses_precision")
	}()

	// Insert data with multiple values
	now := time.Now().UTC().Truncate(time.Microsecond)

	batch, err := conn.PrepareBatch(ctx, `INSERT INTO test_batch_insert_loses_precision (id, created_at)`)
	require.NoError(t, err)
	err = batch.Append(int32(1), now)
	require.NoError(t, err)

	err = batch.Send()
	require.NoError(t, err)

	// Query the data back
	rows, err := conn.Query(ctx, "SELECT id, created_at FROM test_batch_insert_loses_precision WHERE id = 1")
	require.NoError(t, err)
	defer rows.Close()

	// Read the results
	var (
		id        int32
		createdAt time.Time
	)

	require.True(t, rows.Next())
	err = rows.Scan(&id, &createdAt)
	require.NoError(t, err)

	// Verify the data matches what we inserted
	require.Equal(t, int32(1), id)
	require.Equal(t, now, createdAt)
}

[titanproger]

Try this:

now := time.Now().UTC().Truncate(time.Microsecond) err = conn.AsyncInsert(ctx, INSERT INTO test_async_insert_questionmark_loses_precision (id, created_at) VALUES (@param_id, @param_created_at) , true, clickhouse.Named("param_id", 1), clickhouse.DateNamed("param_created_at", now, clickhouse.MicroSeconds)) require.NoError(t, err)

[laeg]

@txomon did @titanproger's workaround help?

[txomon]

Hello @laeg I'm afraid that workaround is not good for us, we are using embedded tuples, and the support for those in the parametrized inserts is not good, so we really need to go for block inserts.