Error parse timestamp #1488
Description
Hello
When @timestamp field has timezone like @timestamp DateTime64(9,
'Europe/Berlin')
the error has happened:
Jun 25 13:19:23.436 ERR quesma/parsers/elastic_query_dsl/query_parser.go:383 > error converting id to sql: invalid timestamp format: '2025-06-16 12:59:59.985233345 +0200 CEST'
I try to change field to other timezone with the same error.
I don't know Go, but I will assume the problem is in platform/util/dates.go
// e.g. '2024-12-21 07:29:03.123456789' -> 9, as it has 9 digits after the dot.
24 // It only works for timestamps looking like this: '2024-12-21 07:29:03[.digit+]'
25 // For timestamps without dot, it returns 0 (e.g. '2024-12-21 07:29:03').
26 func FindTimestampPrecision(timestamp string) (precision int, success bool) {
It calls in platform/parsers/elastic_query_dsl/query_parser.go:360 possible here :
if column, ok := cw.Table.Cols[timestampColumnName]; ok { switch column.Type.String() { case database_common.DateTime64.String(): idToSql = func(id string) (model.Expr, error) { precision, success := util.FindTimestampPrecision(id[1 : len(id)-1]) // strip quotes added above if !success { return nil, fmt.Errorf("invalid timestamp format: %s", id) } return model.NewFunction("toDateTime64", model.NewLiteral(id), model.NewLiteral(precision)), nil }
Using @timestamp without timezone is not comfortable for kibana users.
They need to remember that it is necessary to adjust the time based on the time zone.
Another question is: can we use another type of row id then datetime field?
My table description
CREATE TABLE logs_test.kota_logs ( id String DEFAULT generateULID(), @timestamp DateTime64(9, 'Europe/Berlin'), level String, appName String DEFAULT '', facility Nullable(String), message String DEFAULT '', <... skip> ) ENGINE = MergeTree PARTITION BY (toYYYYMM(@timestamp), xxHash32(appName) % 16) ORDER BY @timestamp TTL toDateTime(@timestamp) + toIntervalDay(90) SETTINGS index_granularity = 1024, ttl_only_drop_parts = 1;