Time range filter not working with Trino.

[rvazquezglez]

I have created a query for Trino DB using SQL explorer, one of the columns is a time column (timestamp(3) with time zone type on Trino), if I try to apply a time filter over that column (either the same SQL explorer or a filter box on a dashboard), I'm getting this error:

Error: line 19:19: Cannot apply operator: timestamp(3) with time zone <= varchar(26)

Expected results

Date filter applied and get results

Actual results

Getting an error.

Screenshots

How to reproduce the bug

1. Setup a connection to a Trino instance
2. Visualize a table with a timestamp or date column
3. Try to apply a date or timestamp filter
4. See error

Environment

• superset version: 1.2.0
• python version: 3.8
• node.js version: not present
• any feature flags active: not activated any.

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

I believe the root cause is that Trino (formerly PrestoSQL) doesn't automatically convert VARCHAR to TIMESTAMP or DATE, and this could be fixed by adding the word date or timestamp in the query.

If I go to "view query" I see the date is passed as a varchar:

WHERE seated_time >= '2021-08-11 00:00:00.000000'
  AND seated_time < '2021-08-12 00:00:00.000000'

[rvazquezglez]

I was able to work around this by NOT selecting a time column, enabling ENABLE_TEMPLATE_PROCESSING and using templates like:

WHERE objectid_timestamp(_id) >= parse_datetime('{{from_dttm}}','YYYY-MM-dd''T''HH:mm:ss') 
  AND objectid_timestamp(_id) < parse_datetime('{{to_dttm}}','YYYY-MM-dd''T''HH:mm:ss') 

[rvazquezglez]

There are some graphs that require a time column (so the workaround doesn't work for them), for example, area charts:

After selecting a time column

The error comes up again:

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[iercan]

We are experiencing same issue. It is broken after we changed sqlalchemy-trino==0.4.1 with trino==0.315.0. I reverted package and problem solved. Seems like there is a compatibility issue with new trino client

[drummerwolli]

bump. still a problem in 2023 ...

[cristovamperes]

same problem using amazon athena

[michael-s-molina]

It seems this is fixed in the latest supported versions 3.0.4 and 3.1.1. Please, feel free to reopen if that's not the case.

[dinoel93]

I have same problem with Superset Version: 3.1.1

[rusackas]

@dinoel93 rather than re-opening this old (and somewhat convoluted) thread, would you be willing to open a new issue, preferably with some updated context and a reproduction flow?

[dinoel93]

@rusackas , done: #27621