BUG with Time in Tableau

[theSaintBelial]

Hi, nice connector!

But I cannot resolve some problem with time.

Tableau trying to round my time to the beginning of the day anyway. For example if I set up filter "Last 24 hours", I can see only data the previous day. From 00:00:00 prev day to 00:00:00 today. But it should be 14:00 prev day to 13:59 today.

I use official Clickhouse JDBC driver v0.3.1-patch from here https://github.com/ClickHouse/clickhouse-jdbc/releases.

[yurifal]

please show me the exact formula of your MINUTE(event_time) Pill on Columns (by double-clicking on it). Does it happen to be the DATEPART() ? or may be the DATETRUNC() ? Looks like the latter.

I did reproduced your case on my test data. Looks like a bug (dunno which one). Stay tuned.

[theSaintBelial]

please show me the exact formula of your MINUTE(event_time) Pill on Columns (by double-clicking on it). Does it happen to be the DATEPART() ? or may be DATETRUNC() ? Expecting the former.

You are right. It's DATETRUNC('minute', [event_time]).

[yurifal]

could you please change the value for the format-datetime-literal key at the end of the dialect.tdd ?

<format-datetime-literal formula="parseDateTimeBestEffortOrNull('%1')" format='yyyy-MM-dd HH:mm:ss' />

expecting the view to render better ;-)

[theSaintBelial]

could you please change the value for the format-datetime-literal key at the end of the dialect.tdd ?

<format-datetime-literal formula="toDateTime(parseDateTimeBestEffortOrNull('%1'))" format='yyyy-MM-dd HH:mm:ss' />

expecting the view to render better ;-)

Thx a lot, it works!!! But I just changed function in your formula to "toDateTime64(strDate, 3)" because I have all datetime data in my DB as DateTime64. With your one I get this error:

An error occurred while communicating with the ClickHouse (JDBC) by Analytika Plus data source 'datasource'
Bad Connection: Tableau could not connect to the data source.
Error Code: FAB9A2C5
ClickHouse exception, code: 169, host: somehost, port: someport; Code: 169, e.displayText() = DB::Exception: Key expression contains comparison between inconvertible types: Nullable(DateTime64(3)) and Nullable(DateTime) inside event_time >= toDateTime(parseDateTimeBestEffortOrNull('2021-09-25 12:00:00')) (version 21.6.5.37 (official build))

Connector Class: clickhouse_jdbc, Version: 0.0.2
SELECT "sometable"."event_time" AS "event_time"
FROM "someschema"."sometable" "sometable"
WHERE (("sometable"."event_time" >= toDateTime(parseDateTimeBestEffortOrNull('2021-09-25 12:00:00'))) AND ("sometable"."event_time" < toDateTime(parseDateTimeBestEffortOrNull('2021-09-25 18:00:00'))))
GROUP BY "event_time"

Finally in my case it looks like:

<format-datetime-literal formula="toDateTime64(parseDateTimeBestEffortOrNull('%1'), 3)" format='yyyy-MM-dd HH:mm:ss.000' />

[yurifal]

Casting a datetime literal to DateTime64 couldn't help -- at least for now, -- as Tableau is expecting the dialect to keep datetime value range as DateTime32.

[yurifal]

Fixed in the recent commit.