[db_specific] CockroachDB does not support to_timestamp

[korpa]

A clear and concise description of what the bug is.

Expected results

Graph should appear

Actual results

Error is shown:

CockroachDB Error

unknown function: to_timestamp()

Screenshots

Without timefilter:

With timefilter:

How to reproduce the bug

Create a graph on database "CockroachDB" and add a time filter

Environment

(please complete the following information):

• superset version: 1.2
• python version: 3.8
• node.js version: node -v
• CockroachDB 21.1.6

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

In Superset 1.0 CockroachDB works fine with time filters. After upgrading to 1.2 this issue appears

[junlincc]

Hi @korpa , we may not get to any db specific issues soon enough, feel free to open a PR, we are happy to review!

[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.

[korpa]

This issue still exists. Please do not close

[korpa]

It seems that someone implemented this feature in CockroachDB cockroachdb/cockroach#82523

Hopefully this will released in a minor version that we do not have to wait another 6 months for the next big release of CockroachDB

[leecjohnny]

Currently, I face this error when using Version: 2023.23.1 via preset.io.

Specifically, it says Error: unknown signature: to_timestamp(string, string)

CockroachDB doesn't have that exact to_timestamp function that returns a timestamp-like datatype. Though it does have strptime(input: string, format: string) → timestamptz

https://www.cockroachlabs.com/docs/stable/functions-and-operators.html#date-and-time-functions

is this a sqlalchemy driver issue or superset?

driver: https://github.com/cockroachdb/sqlalchemy-cockroachdb

[elewis787]

Any advice for this issue? - running into this as well and am happy to dig in if there is some rough guidance on where the root issue may exist.

The workaround I am doing is roughly as follows

• Created a dataset that includes a date column ( i.e created_dttm )
• Edited the dataset columns, flagging the date column as non temporal
• Use jinja templating and build in from_dttm and to_dttm

This prevents the query generator from adding the to_timestamp when filtering with a time range.

[elewis787]

@yousoph @junlincc - seems like this is a matter of updating the db spec for crdb here https://github.com/apache/superset/blob/master/superset/db_engine_specs/cockroachdb.py

I am going to play around with getting a local superset env set up and will see if I can validate that is the correct spot to implement how to handle time filters.

[elewis787]

I have a fix for this running locally and in a prod env using version 2.1.0.

The PR that should address this is #24711