Dear Experts,
I have a table with a timestamp column, and I'd like to query it for date ranges such as "all of 2010", "all of May 2020", "25th December 1991" etc. i.e. things that are prefixes of the date-time when expressed as a string. So naively I thought I would try to use "like" patterns:
timestamp like "2010-%"
timestamp like "2020-05-%"
timestamp like "1991-12-25%"
That gives the right answers. But I think it's always doing sequential scans. Is there any way that it can do this using an index? In principle a "like" query that just tests a prefix should be implementable using an index, shouldn't it?
An index is used if I rewrite the query as e.g. timestamp >= '2020-05' and timestamp < '2020-06', but forming that sort of query is a bit tedious in general due to the complexities of dates.
How would you do this?