Timerange filter empty result

[netliteIT]

Bug description

Importing a public data set or using a dataset on an external DB using "ts" TIMESTAMP WITH TIME ZONE, works fine but using wizard to manage a simple timerange produce an empty result.

How to reproduce the bug

Follow the first steps of the guide:
https://preset.io/blog/timeseries-cratedb-superset/
CREATE TABLE IF NOT EXISTS "doc"."power_consumption" (
"ts" TIMESTAMP WITH TIME ZONE,
"Global_active_power" REAL,
"Global_reactive_power" REAL,
"Voltage" REAL,
"Global_intensity" REAL,
"Sub_metering_1" REAL,
"Sub_metering_2" REAL,
"Sub_metering_3" REAL,
"meter_id" TEXT,
"location" GEO_POINT,
"city" TEXT
)
CLUSTERED INTO 4 SHARDS

Populated with sample data.

Now set a filter on ts like that:

Looks like the generated query has a .0 at the ond of timestamps which is not understood by internal superset engine and by external DB.

This is the query generated by SuperSet:

SELECT DATE_TRUNC('day', ts) AS ts,
sum("Global_active_power") AS "SUM(Global_active_power)"
FROM doc.power_consumption
WHERE ts >= 1172707200000.0
AND ts < 1206748800000.0
GROUP BY DATE_TRUNC('day', ts)
ORDER BY "SUM(Global_active_power)" DESC
LIMIT 10000

I've tested on external CrateDB and on imported data.
The result is the same.

Screenshots/recordings

No response

Superset version

3.1.1

Python version

3.9

Node version

16

Browser

Firefox

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[hlcianfagna]

I am thinking that perhaps we need a (...)::BIGINT in

• superset/superset/db_engine_specs/crate.py

  Line 49 in 56a6660

  return "{col} * 1000"

• superset/superset/db_engine_specs/crate.py

  Line 62 in 56a6660

  return f"{dttm.timestamp() * 1000}"

• and perhaps also

  superset/superset/db_engine_specs/crate.py

  Line 53 in 56a6660

  return "{col}"

  
  It needs to be tested

[hlcianfagna]

cc @Vitor-Avila re https://preset.atlassian.net/browse/PPR-714