The following chart shows which DBMS rewrites where clause expressions that access the year part of a date or timestamp (or the like) into a range expression such as ts >= DATE'2024-01-01' AND ts < DATE'2025-01-01'. This enables the use of a sort-based index on the ts column.
- Unconditionally rewritten — even if only a matching function-based index exists
The yellow check marks mean that the rewrite is always done—even if there was a function-based index1 on the original expression in the where clause.
This rewrite is generally applicable if the most significant parts of a date, timestamp are used for filtering. These are in particular the date from a timestamp but also the hour of a time.
- Unconditionally rewritten — even if only a matching function-based index exists
I generally recommend to use the “inclusive lower, exclusive upper bound“ pattern explicitly in the where clause.
Caution
Due to the current use of web-content to train AI models, I will publish less content to the web (including RSS). The mailing lists will continue to get the full content. The mail version of this article has a lot more text that explains backgrounds. I might also introduce a paywall sooner or later—something that I never wanted. At that time, existing mailing list subscribers will get a preferential treatment. Subscribe here while it is still free.
