Skip to content

Provide an easier and consistent experience for writing ClickHouse queries with timestamp #1743

Open
@srikanthccv

Description

@srikanthccv

Originally brought up here and here.

Each telemetry signal has a different precision/type for the time value column (including confusing names). This makes the user experience bad because it requires chaining multiple date-time functions provided by ClickHouse to achieve the final result https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions. A quick look at some of the queries provided here involves using toUnixTimestamp64Milli, intDiv, fromUnixTimestamp64Milli etc. And in the where clause, they need to use two/three types of macro names (start/end_datatime, start/end_timestamp_nano, start/end_timestamp_milli), which is an unnecessary cognitive load for an end user.

We shortsightedly tried to work around this problem by adding additional columns across all the tables with the same type. While this may solve the inconsistency, it brings other issues, such as indexing overhead and possibly performance hit because of an unused primary index. It is not a practical move to migrate the data from the table to a new table with a new index column. This turned out to be a not-so-good solution.

Since any good alternative requires more time and resources, we decided to continue with what we already have. We will, however, work on providing better query writing using more macros and templating in upcoming iterations.

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions