Time field of type UInt64 not detected in log explorer

[PierreNowak]

Signoz uses timestamp column with UInt64 type, TimeField does not give the possibility to cast toDateTime(timestamp / 1000000000) and use it as TimeField.

[asimpson]

Is there a way we can reproduce this to verify and fix the issue?

[PierreNowak]

yes sure,

1. spawn a clickhouse server
2. Create a table with this schema :

CREATE TABLE default.test_grafana
(
    `timestamp` UInt64 CODEC(DoubleDelta,
 LZ4),
     `body` String CODEC(ZSTD(2))
)
 ENGINE = MergeTree()
PARTITION BY toDate(timestamp / 1000000000)
ORDER BY (timestamp)

3. go to grafana explore tab, select format= Logs, select table and observe you can't filter on UInt64 field.

to make it work in classic widgets I transform timestamp with : toDateTime64(timestamp/1000000000,9) AS log_time,

[mshustov]

Time field expects a column with Date* or DateTime* type.
For your case, maybe the plugin should allow a custom input with toDateTime*(..) or toDate*(..)?

[asimpson]

Circling back to this, what we're really talking about is transformation of a field to a time field correct? We could add another row (before the time field row) that lists any fields that satisfy the type requirements for toDateTime and then provide another dropdown with toDateTime or toDate as options?

[SpencerTorres]

If a time is stored in an integer, it's most likely going to be some form of Unix timestamp (seconds, millis, micros, nanos). I have tested the results of toDateTime64 with varying levels of precision in the input, and it appears that selecting the precision will be necessary. Whether this is done by adding more options in time field dropdown or in a separate dropdown depends on what we want the user experience to be.

For reference, this table uses the following unix nanoseconds timestamp: 1693158835123456789 (ISO: 2023-08-27T17:53:55.123456789Z)

column value	SQL function	result
1693158835 (s)	toDateTime64(toString(col), 0) (s)	2023-08-27 17:53:55
1693158835 (s)	toDateTime64(toString(col), 3) (ms)	2023-08-27 17:53:55.000
1693158835 (s)	toDateTime64(toString(col), 6) (us)	2023-08-27 17:53:55.000000
1693158835 (s)	toDateTime64(toString(col), 9) (ns)	2023-08-27 17:53:55.000000000
1693158835123 (ms)	toDateTime64(toString(col), 3) (ms)	2023-08-27 17:53:55.123
1693158835123 (ms)	toDateTime64(toString(col), 6) (us)	1970-01-20 14:19:18.835123
1693158835123 (ms)	toDateTime64(toString(col), 9) (ns)	1970-01-01 00:28:13.158835123
1693158835123456 (us)	toDateTime64(toString(col), 6) (us)	2023-08-27 17:53:55.123456
1693158835123456 (us)	toDateTime64(toString(col), 9) (ns)	1970-01-20 14:19:18.835123456
1693158835123456789 (ns)	toDateTime64(toString(col), 9) (ns)	2023-08-27 17:53:55.123456789

As you can see, there is no common precision value that works for all of these values.

The toString function is also necessary, but only to preserve the precision in the case of nanoseconds. If toString is not provided, the value must be converted to a decimal (1693158835123 -> 1693158835.123). I haven't tested how either option would affect performance for larger datasets.

The other option would be to simply allow for custom input in this box, however the user would need to be aware of these quirks when converting the value. In the case of this issue, the solution that preserves the original precision of the UInt64 timestamp would be toDateTime64(toString(col), 9).

I think conditionally displaying a separate box for selecting precision would be the most user-friendly. If the user must manipulate the input beyond this (string types, or non-unix epochs), it will have to be manually written with the SQL editor.

Here is an example of what this box might look like when selecting a column with the number type:

With this information, the query generator will know which function to use. We could also extend this to support string types with a dropdown for common formats.

Let me know what you think

[mshustov]

The other option would be to simply allow for custom input in this box, however the user would need to be aware of these quirks when converting the value. In the case of this issue, the solution that preserves the original precision of the UInt64 timestamp would be toDateTime64(toString(col), 9).

Why not? Our customers are very technical users.

I think conditionally displaying a separate box for selecting precision would be the most user-friendly.

Agreed, it seems to be the most user-friendly and easy-to-use option. @asimpson @bossinc are you okay with the proposed UX?

[bossinc]

@mshustov Yes, the proposed UX looks good!

[asimpson]

Agreed, this is great. 🏆

[SpencerTorres]

In v4 you can now type in your own casting function in the time field.

Being able to type any function into the field is the most flexible, but we can re-open this if we want to re-visit the proposed UI change