Time filter in native SQL query throws cannot cast error

Hi, I’m writing a Postgres native SQL query and I am adding filters manually.

SELECT count(*) AS “count”, (date_trunc(‘week’, CAST((CAST(to_timestamp(“public”.“my_database”.“time”) AS timestamp) + INTERVAL ‘1 day’) AS timestamp)) - INTERVAL ‘1 day’) AS “time”

FROM “public”.“my_database”

WHERE {{City}} AND {{Shape}} AND {{Times}}

etc…

‘City’ and ‘Shape’ filters work well, whereas the ‘Time’ filter once selected for throws the error:

ERROR: cannot cast type integer to date Position: 417

Wonder what could cause this and how I could fix it. I have selected the time column as a UNIX timestamp in Data Model.

Thanks.

Hi,
it sounds a bit like this known issue: https://github.com/metabase/metabase/issues/4058.
Maybe you could add your details in github (in case it fits - I am no postgres user) to give the metabase guys more info to work on.
Cheers, Eva

Probably related, database sync produces:

Error generating fingerprint for Field 312 ‘time’: java.lang.String cannot be cast to java.lang.Number

Thanks I’ve filed it :slight_smile: