DateTime64 Support

[chr-istoph]

Thanks for this great plugin!

DateTime64 is the preferred Clickhouse data type for sub-second precision timestamps:
https://clickhouse.tech/docs/en/sql-reference/data-types/datetime64/

Is DateTime64 support on the horizon for this plugin?

Namely

• Time range selection in the query builder (e.g. WHERE Timestamp BETWEEN toDateTime64(1598007073,3) AND toDateTime64(1598028056,3))
• Display of sub-second precision return time series (e.g. sensor values logged at 10 Hz)

[Slach]

@chr-istoph
according to grafana/grafana#3035
grafana itself supports only milliseconds resolution right now

so, for quick workaround you can try to use something like that
SELECT intDiv(toUInt64(datetime64_precision_3_field),$__interval_ms) AS t ... and
WHERE t BETWEEN toDateTime64($__from,3) AND toDateTime64($__to,3))
with grafana-clickhouse

[chr-istoph]

@Slach
Thanks for the pointer 👍
This works a treat:

• intDiv(toFloat64(datetime64_field)*1000, $__interval_ms) * $__interval_ms) as t

• WHERE t BETWEEN toDateTime64($__from,3) AND toDateTime64($__to,3))

[mikhno-s]

Hey, similar ussue.

I have changed timestamp column type to DateTime64 and found out that my dashboard stopped to work.
Is it complex task to add DateTime64 as allowed column type to search?

it should be interpolated in row query into workaround as in upper messages.

Or if it is already done - how can I chose my DateTime64 field as a timestamp column?

[kakvablyat]

+1

[Slach]

@chr-istoph @kakvablyat @mikhno-s could you check latest master version?

[Slach]

@chr-istoph @kakvablyat @mikhno-s
you should change datetime column type to Column:DateTime64 in your queries

[mikhno-s]

Cannot save that parameter. It returns to DateTime.

However, this does not work for me:

...
intDiv(toFloat64(datetime64_field)*1000, $__interval_ms) * $__interval_ms) as t
...
WHERE t BETWEEN toDateTime64($__from,3) AND toDateTime64($__to,3)) ❌ 
...

The only way I made it work:

WHERE $dateTimeCol BETWEEN toDateTime64($__from/1000, 3) AND toDateTime64($__to/1000,3) ✅ 

UPD:
plugin - v2.1.0
grafana -v7.1.5
clickhouse - v20.3.16

I've not tried last plugin ver, but I see that without /1000 it does not work anyway.
The cause, for example $__to transforms to 1604267999000, that becomes 1604267999000.000 after toDateTime64 transformation.

SELECT toDateTime64(1604267999000, 3)
┌─toDateTime64(1604267999000, 3)─┐
│          2028-02-15 :43:20.000 │
└────────────────────────────────┘

[mikhno-s]

Also, possible solution for grafana > v7.1.2

WHERE $dateTimeCol BETWEEN toDateTime64(${__from:date:seconds}, 3) AND toDateTime64(${__to:date:seconds},3)

[Slach]

@mikhno-s @chr-istoph @kakvablyat could you check latest master again?
I added support DateTime64 to $timeFilter and $timeSeries
please look to https://github.com/Vertamedia/clickhouse-grafana/blob/master/docker/grafana/dashboards/test_datetime64_support.json for dashboard example

[Slach]

@mikhno-s could please try to use latest master version

and look to ClickHouse/ClickHouse#16655

[vbichov]

@Slach doesn't work:
the generated sql for $timeSeries with min step of 60s is:
(intDiv(toFloat32(timestamp) * 1000, 60) * 60) as t

while it should be:
(intDiv(toFloat32(timestamp) * 1000, 60000) * 60000) as t

[Slach]

@vbichov which DateTime64 type do you use? DateTime64(3) or DateTime64(6) ?

[mikhno-s]

@Slach DateTime64(3)

[Slach]

@mikhno-s , @vbichov could you try latest master again?

[vbichov]

Works for me - thanks.