Add support for Datetime64 type in ClickHouse connector

[ebyhr]

See TODO comment of "https://github.com/trinodb/trino/issues/10537" in ClickHouseClient

[ebyhr]

https://github.com/trinodb/trino/issues/10538#issuecomment-1020668450

[tangjiangling]

I'd like to work on this, please assign it to me.

[tangjiangling]

After reading this comment, my understanding is that now we need to support not only ClickHouseDate/DateTime, but also ClickHouseDate32/DateTime64, and ClickHouseDate32 mapping to Trinodate and ClickHouse DateTime64 mapping to Trinotimestamp(n) with time zone, besides these, all the points mentioned in this comment need to be implemented, do I understand it right?

[ebyhr]

The difference between DateTime and DateTime64 is sub-second precision as far as I know, so timestamp(n) without time zone looks better.

[tangjiangling]

My mistake, after reading the ClickHouse documentation carefully, you are right.

[tangjiangling]

One more question:

Do we need to support ClickHouse DateTime*(timezone)? I see that we are now letting the user control how to handle data types not supported by Trino via the parameter unsupported-type-handling.

[ebyhr]

@tangjiangling Sorry, I was missing your comment. Yes, it would be nice to support DateTime*(timezone) as well.

[tangjiangling]

@ebyhr Never mind, I will continue this in the near future.

[eshirvana]

@tangjiangling are you still working on this ? any ETA?

[tangjiangling]

are you still working on this ?

Yes.

any ETA?

Not sure, I already have a pending PR for supporting DateTime64, my plan is to wait until this PR is merged before submitting.

CC @hashhar

[hashhar]

Thanks for the reminder @tangjiangling . I'll take another look.

[eshirvana]

@ebyhr @hashhar @tangjiangling Let me know if I can help in any way to accelerate this

[tangjiangling]

I already have a pending PR for supporting DateTime64, my plan is to wait until this PR is merged before submitting.

@eshirvana Sorry for the delay, the PR mentioned here should be merged today or tomorrow and I will file another PR in time for DateTime64 support then (maybe this weekend, you can keep en eye on this)

[eshirvana]

@tangjiangling awesome great ,thank you would you please link/tag the new pr you will merge next week here as well?

[eshirvana]

@tangjiangling hi , any good news yet ? ;) if there is anything I can help to accelerate this please let me know...

[tangjiangling]

@eshirvana see #15040

[eshirvana]

@tangjiangling

Cool , thanks , does this mean trino will be able to pushdown datetime64 predicates with this pr?

[tangjiangling]

does this mean trino will be able to pushdown datetime64 predicates with this pr?

yep

[riteshvaryani]

hi, are there plans to merge this pr in trino?

[mahdimalverdi]

Hi, I am working with a table in ClickHouse that contains a timestamp(0) column with a time zone. I encountered an issue when trying to filter the data using the following query:

SELECT *  FROM table WHERE date >= Date( '2024-08-19 00:00:00.000')  AND date < Date( '2024-08-31 00:00:00.000') limit 10 ;

The query seems to attempt loading all data, which impacts performance significantly. However, when I modify the query to remove the milliseconds, as shown below, the issue is resolved:

SELECT *  FROM table WHERE date >= Date( '2024-08-19 00:00:00')  AND date < Date( '2024-08-31 00:00:00') limit 10 ;

Problem: The original query with milliseconds (.000) causes ClickHouse to load all data. The problem is avoided by removing the milliseconds from the timestamp.