Enable Efficient Date Filtering on String-Partitioned Athena Tables

Nobody has claimed this yet.

**Is your feature request related to a problem? Please describe.**

We need to be able to leverage field filters on dates stored as strings in Athena. AWS recommends storing dates as strings for optimizing partition performance:

- [Athena Notable Limitations](https://docs.aws.amazon.com/athena/latest/ug/other-notable-limitations.html)
- [Athena Data Types](https://docs.aws.amazon.com/athena/latest/ug/data-types.html)

In Athena you can simply query the dates like so:

```
WHERE "s3-data-source"."partition_test"."col2" BETWEEN '2023-11-01'
AND '2024-01-01'
```

If you follow this recommendation then you must cast the string to a temporal type in order to leverage field filters in Metabase. As the data volume grows and you leverage partitions - some partition pruning can still occur while the strings are cast (depending on how the tables and partitions are set up). However, as the volume of partitions grow the query execution plan time starts to increase because it take so long for metadata to come back from Glue.

Athena offers [partition indexes in Glue ](https://docs.aws.amazon.com/glue/latest/dg/partition-indexes.html) to speed up metadata retrieval. This works great in Athena but these indexes aren't being leveraged by the queries from Metabase due to the datetime coercion. This means that queries run in Metabase with field filters are much much slower than those in run directly in Athena without the date coercion.

**Describe the solution you'd like**

Some option (such as a special coercion type) that allows the use of field filters on these string columns so that we can retain the use of the "All Options" date picker without having to modify the entire configuration of the Athena DW (or add a bunch of operational overhead for manual maintenance of partition projection).

Like the above example or by casting on the right:

```
WHERE "s3-data-source"."partition_test"."date_hour" BETWEEN CAST(timestamp '2024-08-09 09:00:00.000' AS string) AND CAST(timestamp '2024-08-09 10:00:00.000' AS string)
```

**Describe alternatives you've considered**

**1. Athena offers [partition projection]**(https://docs.aws.amazon.com/athena/latest/ug/partition-projection.html) which allows you to set up definitions for partition metadata directly on the tables. This allows you to skip the Glue or Hive metadata catalogues completely. It sounds like a potential solution but it requires a lot of manual work and added maintenance overhead to go this route.

**2. Using SQL variables in the native question and leveraging multiple filters date filters:**

We could set up the SQL in the native editor like so:

```
where "created at" >= DATE_FORMAT({{startDate}}, '%Y-%m-%d %H:%i:%s')
and "created at" <= DATE_FORMAT({{endDate}}, '%Y-%m-%d %H:%i:%s')
```

The user experience here is not good, though. Also, users have grown accustom to the "All Options" date picker and to move to a solution like this we would have to walk back this functionality on all dashboards for all users.

**How important is this feature to you?**

Very important. Right now partition projection seems to be the only option that could get us to the solution we need (retaining the full date picker user experience) - but this is a big lift operationally and we haven't yet been able to validate that it will work. If it does, as the data structure changes the partitions have to be manually updated on the individual tables. We lose the automated detection of new partitions that Glue provides. There are considerations for data retention policies that can impact performance by leaving empty partitions, etc. So the active maintenance of this creates significant operational overhead.

It would be great if we could just query these with field filters the way we can query them in Athena.

**Additional context**
[Internal slack convo](https://metaboat.slack.com/archives/C64DB8QH2/p1723220360350049)

No source file, test, or entry point is named. Start by tracing how Metabase generates Athena field-filter predicates and applies datetime coercion; compare the generated SQL with the string-based examples and verify that partition indexes remain usable while the date-picker experience is preserved.

Written by the indexing model from the issue text.