Time filtering - Not working on calculated fields

[ValentinC-BR]

When using a calculated column as the TIME COLUMN, the preset filters (actually all the filters in the time section) do not work.
The calculated field (date or datetime) is compared to a STRING, causing the SQL request not to work.

Expected results

The calculated column should be compared to a DATE or DATETIME value when the user applies a filter

Actual results

The calculated column is compared to a STRING value

Screenshots

TABLE CONFIGURATION

CHART CONFIGURATION

GENERATED QUERY

How to reproduce the bug

1. Create a calculated column, with a date or datetime format
2. Select it as THE time column
3. Chose any time filter (last month for instance)
4. RUN query

Environment

(please complete the following information):

• superset version: 1.0.1
• python version: 3.7.9
• node.js version: doesn't apply, I run on Kubernetes, using gunicorn as server
• source : Athena

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

None

[ValentinC-BR]

I made a mistake while taking the screenshots (the ' character shouldn't be present). Sorry !

The error is present anyway, it is not related to it.

[zhaoyongjie]

It seems that Athena is not implicitly converted from string to datetime-like. This problem may exist in Athena / Clickhouse / Kylin2.

@ValentinC-BR
could you try this SQL using SQLLab, and change the where clause to like this:

....
WHERE date_add('day', 10, subscription_date) >= cast('2021-02-19 00:00:00' as date)
AND  date_add('day', 10, subscription_date) < cast('2021-03-19 00:00:00' as date)
... 

Can it query?

[ValentinC-BR]

I get the following error : Value cannot be cast to date: 2021-02-19 00:00:00

Casting it as TIMESTAMP work, however.

[zhaoyongjie]

ok. thanks for this debug. We will improve the corresponding SQL generation logic.

@junlincc

[junlincc]

@ValentinC-BR feel free to push a fix if it's urgent. 🙏 thanks for reporting

[zhaoyongjie]

closes by #14813

[john-bodley]

@ValentinC-BR regarding your issue I was wondering whether you tried setting the calculated column type—in the same modal where you defined the SQL expression? The reason I ask is I believe that is the correct mechanism for addressing your issue.

See #16950 for details.

[ValentinC-BR]

Yes I did. But it wasn't working at that time.
Now it seems to work, however Superset still doesn't support some data types like "Timestamp with Timezone" (I think there's already an issue around this)