Field Filter doesn't work with BigQuery DATETIME fields

Bug reports
1

Hi All,

Recently, in of our BigQuery tables, we modified data type of a column from TIMESTAMP to DATETIME.

Ever since then, some of our Metabase questions that used the particular table have stopped working.

I tried creating a new question with BigQuery Native SQL.

Metabase Query:

SELECT
    Column_1,
    Column_2,
    Column_3
  FROM
    `dataset.table`
  WHERE
    {{CreatedDate}}

Bigquery Query:

SELECT
    Column_1,
    Column_2,
    Column_3
FROM
    `dataset.table`
WHERE
    timestamp_trunc(`dataset.table`.`CreatedDate`, day, 'Asia/Kolkata') = timestamp "2024-02-23 00:00:00 Asia/Kolkata"

Even though column CreatedDate is DATETIME, Metabase assumes it is TIMESTAMP and tries to apply the timestamp function instead of datetime function, which then fails.

BigQuery Error:

 { "code": 400, "errors": [ { "domain": "global", "location": "q", "locationType": "parameter", "message": "Query error: No matching signature for function TIMESTAMP_TRUNC for argument types: DATETIME, DATE_TIME_PART, STRING. Supported signatures: TIMESTAMP_TRUNC(TIMESTAMP, DATE_TIME_PART, [STRING]); TIMESTAMP_TRUNC(DATETIME, DATE_TIME_PART) at [19:9]", "reason": "invalidQuery" } ], "message": "Query error: No matching signature for function TIMESTAMP_TRUNC for argument types: DATETIME, DATE_TIME_PART, STRING. Supported signatures: TIMESTAMP_TRUNC(TIMESTAMP, DATE_TIME_PART, [STRING]); TIMESTAMP_TRUNC(DATETIME, DATE_TIME_PART) at [19:9]", "status": "INVALID_ARGUMENT" }

Weird thing is, on searching I found this old issue on github which says it's fixed

But I am using Metabase v0.47.4 and still facing the issue.

Edit:

In the above query, if I modify

timestamp_trunc(dataset.table.CreatedDate, day, 'Asia/Kolkata') = timestamp "2024-02-23 00:00:00 Asia/Kolkata"

to

timestamp_trunc(dataset.table.CreatedDate, day) = datetime "2024-02-23 00:00:00"

it starts working, but of course I can't do this within Metabase as field filter logic is hardcoded.