trino-python-client returns dtype string for datetime/timestamp fields

[RJ3]

presto-client returns data type of string for a timestamp field.
Setup:

• PrestoSQL 333
• PostgreSQL 12.1
• presto-client 0.300.0

Tested:

1. Using the PrestoSQL JDBC driver, the field is returned as a Java timestamp.
2. Using psycopg2 driver with SQLalchemy directly to Postgres, the field is returned as a python datetime object or pandas timestamp object.
3. Using presto-client 0.300, the field is returned as a string.

Expectation:
The field should be returned as a python datetime object or pandas timestamp object, akin to the PostgreSQL driver.

Note:
The issue has also been reported for pyhive (no longer under current development). I have not tested PrestoDB's version to see if the other team has also worked on the topic.

[findepi]

The biggest question is how to avoid breaking users.
For now we should implement napping to datetime behing a toggle (init param).
At some point, we probably should consdier switching the default, as we do not want to maintain inferior representation for ever.
cc @electrum

[alexbrisan]

@findepi I would like to contribute a fix for this. Would you like me to detail a fix first, or just submit a Pull Request?

[findepi]

@alexbrisan awesome!

it's worthwhile to have a conversation before doing work, if the work would take a lot of time.
Perhaps this won't be the case here, will it?

Things to consider

• what do other drivers do? eg. postgresql's or oracle's?
• do we have a toggle to switch between old & new behavior?
• i am not expert on date/time/timezones handling in Python, but it seems the standard library doesn't cover all the use-cases (hence pytz exists).
  • this is why checking with oracle's driver can be interesting. PostgreSQL doesn't have SQL standard-conforming timestamp with time zone data type and both Trino and Oracle do.

[alexbrisan]

I will take a look at the oracle one and come up with a plan!

[buremba]

The issue cases Superset to fail creating datasets that have DATE/TIMESTAMP columns. I can also send a PR if there is any interest to fix it but #81 seems to fix the issue already.

[mdesmet]

I think this issue can be closed as we merged #160.

[ebyhr]

@mdesmet Closed. Thanks for your contribution!