otel_trace_id_ts Loss of Precision in Start and End Timestamps Due to Type Mismatch

[avivtabak]

Component(s)

exporter/clickhouse

Is your feature request related to a problem? Please describe.

Currently the %s_trace_id_ts table description is as follow:

CREATE TABLE IF NOT EXISTS "%s"."%s_trace_id_ts" %s (
    TraceId String CODEC(ZSTD(1)),
    Start DateTime CODEC(Delta, ZSTD(1)),
    End DateTime CODEC(Delta, ZSTD(1)),
    INDEX idx_trace_id TraceId TYPE bloom_filter(0.01) GRANULARITY 1
) ENGINE = %s
    PARTITION BY toDate(Start)
    ORDER BY (TraceId, Start)
    %s
    SETTINGS index_granularity=8192, ttl_only_drop_parts = 1

And the materialized view definition is

CREATE MATERIALIZED VIEW IF NOT EXISTS "%s"."%s_trace_id_ts_mv" %s
TO "%s"."%s_trace_id_ts"
AS SELECT
              TraceId,
              min(Timestamp) as Start,
              max(Timestamp) as End
   FROM "%s"."%s"
   WHERE TraceId != ''
   GROUP BY TraceId

While in traces_table the definition of Timestamp is Timestamp DateTime64(9) CODEC(Delta, ZSTD(1))

The Start and End fields in the target table are defined as DateTime, which results in a loss of nanosecond precision when aggregating Timestamp values from the source table. This truncation causes a loss of accuracy, especially for short-duration traces, where nanosecond-level granularity is significant.

Describe the solution you'd like

Suggested Fix
To preserve the original precision, the Start and End fields in the %s_trace_id_ts table should be defined as DateTime64(9) instead of DateTime, matching the data type of the source Timestamp column.

Describe alternatives you've considered

No response

Additional context

No response

[github-actions]

Pinging code owners:

• exporter/clickhouse: @hanjm @dmitryax @Frapschen @SpencerTorres

See Adding Labels via Comments if you do not have permissions to add labels yourself.

[avivtabak]

In addition, i'd like to raise a potential issue regarding the way the materialized view calculates the End timestamp.

From my understanding, this table is intended to represent the full duration of a trace — that is, when it starts and when it ends. However, the current approach seems to derive the End timestamp based on the latest Timestamp value across all spans. This may not always reflect the actual trace end time, especially in edge cases.

For example, if a trace consists of only a single span, calculating End as simply the span's Timestamp results in a duration = End - Start = 0, which does not represent the real span duration.

To better reflect the trace's actual end time, I suggest the following adjustment:

toDateTime64((toUnixTimestamp64Nano(min(Timestamp)) + sum(Duration)) / 1e9, 9) AS End

This calculation ensures the End timestamp includes the durations of all spans, starting from the earliest span.

Of course, it’s possible I’ve misinterpreted the original purpose of the table — if that’s the case, feel free to clarify. But if the goal is to accurately represent the trace duration, this change might help improve its correctness.