ClickHouse ci_finished_builds contains negative queueing_duration values
Ci::Build.started_at
can have NULL values. This leads the ClickHouse ci_finished_builds.started_at
value to default to finished_at
value.
In !138748 (merged), we changed started_at
to default to COALESCE(finished_at, 0)
. After looking at the PG/CH production databases with @vshushlin, we decided to keep the CH as close as possible to the PG database, and instead do any filtering/data massaging on the CH side. We should therefore change date/time stamps in the table to default to 0 to represent NULL values:
`created_at` DateTime64(6, 'UTC') DEFAULT 0,
`queued_at` DateTime64(6, 'UTC') DEFAULT 0,
`finished_at` DateTime64(6, 'UTC') DEFAULT 0,
`started_at` DateTime64(6, 'UTC') DEFAULT 0,
This allows us to keep the data representation as close as possible to the canonical Postgres version of the data, and instead change the duration materialized columns to perform any data massaging needed:
`duration` Int64 MATERIALIZED greatest(0, age('ms', least(started_at, finished_at), finished_at)),
`queueing_duration` Int64 MATERIALIZED greatest(0, age('ms', least(queued_at, started_at), started_at))