Question Builder: Generate SQL with naive dates instead of localized timestamps

[lpyfm]

I'm using Metabase 0.42.0 with PostgreSQL and I noticed that one particular question built with the question builder took very long to complete compared to a native query.

The question is basically "count all rows where column = x", where column has the PostgreSQL type date. In the question builder, everything looks like Metabase correctly detects that this column is a date column. It allows me to specify the time in addition to the date, but this is optional. However, the Metabase logs showed the following:

{:type :query, 
…
:filter
…
[:field 716 {:temporal-unit :default}]
[:absolute-datetime #t "2022-01-01T00:00+01:00[Europe/Berlin]" :default]]
[:< 
 [:field 716 {:temporal-unit :default}]

which already indicates that Metabase is using a localized timestamp instead of a naive date type. The slow query log revealed the following:

 …WHERE (… AND "my_schema"."my_table"."my_date_column" >= $2 AND "my_schema"."my_table"."my_date_column" < $3)
 --  parameters: … $2 = '2021-01-01 00:00:00+01', $3 = '2021-01-22 00:00:00+01'

The slow query log unfortunately does not tell us which type is used for '2021-01-01 00:00:00+01' and '2021-01-22 00:00:00+01', but given the output from the Metabase log and the fact that the string includes timezone information, I would assume it's timestamptz. So based on this assumption, I try to reproduce what Metabase is doing by running a raw SQL query, like this:

…WHERE (… AND "my_schema"."my_table"."my_date_column" >= '2021-01-01 00:00:00+01'::timestamptz AND
"my_schema"."my_table"."my_date_column" < '2021-01-22 00:00:00+01'::timestamptz)

which takes about 56 seconds to complete, which is about the same time it took metabase to complete this query from the question builder. The explain plan shows that no indexes are being used. Next, I try to run a raw SQL query like the one above, but with date types instead of timestamptz types:

…WHERE (… AND "my_schema"."my_table"."my_date_column" >= '2021-01-01'::date AND
"my_schema"."my_table"."my_date_column" < ' 2021-01-22'::date)

This time, the query takes 140ms to complete, and the index is being used.

Proposal

Could you provide the possibility to treat columns of type date as simple, naive dates without any timestamp conversions in the background to make it easier for PostgreSQL (and perhaps other databases) to use indexes?

Motivation

We have tables with more than 100 million rows, and the date column is usually that one column that allows us to run queries that don't take forever to complete. So, it would be a great improvement for us if Metabase generates SQL queries where indexes based on date columns are used whenever possible.

[camsaul]

This may have already been fixed by #40530, need to verify.

[paoliniluis]

@lpyfm can you please check if this has been fixed?