Getting a date range using "like"
(1) By Phil Endecott (endecotp) on 2021-08-08 13:17:55 [link]
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?
(2) By TripeHound on 2021-08-08 15:30:46 in reply to 1 [link]
Can't test at the moment, but I seem to remember that `LIKE` uses a `NOCASE` search, and a default index is case sensitive. You might try making the timestamp column, or the index on it, `NOCASE`.
(3) By TripeHound on 2021-08-08 20:38:30 in reply to 1 [link]
Can confirm my hunch. Using default collation for field and index: ```sql create table t ( timestamp text, data ) ; insert into t values ( '2021-08-02', 'date1' ), ( '2021-08-23', 'date2' ), ( '2021-09-01', 'date3' ) ; create index idx on t( timestamp ) ; .eqp on ``` produces a table-scan: ```sql -- Using default index select * from t where timestamp like '2021-08-%' ; QUERY PLAN `--SCAN TABLE t 2021-08-02|date1 2021-08-23|date2 ``` Replacing the index with a `COLLATE NOCASE` one: ```sql drop index idx ; create index idx on t( timestamp collate nocase ) ; ``` uses the index: ```sql -- Using NOCASE index select * from t where timestamp like '2021-08-%' ; QUERY PLAN `--SEARCH TABLE t USING INDEX idx (timestamp>? AND timestamp<?) 2021-08-02|date1 2021-08-23|date2 ``` Alternatively, creating the original field as `COLLATE NOCASE` and using a default index: ```sql drop table t ; create table t ( timestamp text collate nocase, data ) ; insert into t values ( '2021-08-02', 'date1' ), ( '2021-08-23', 'date2' ), ( '2021-09-01', 'date3' ) ; QUERY PLAN `--SCAN 3 CONSTANT ROWS create index idx on t( timestamp ) ; ``` also uses the index: ```sql -- Using NOCASE collation select * from t where timestamp like '2021-08-%' ; QUERY PLAN `--SEARCH TABLE t USING INDEX idx (timestamp>? AND timestamp<?) 2021-08-02|date1 2021-08-23|date2 ```
(4) By anonymous on 2021-08-08 22:53:26 in reply to 3
This should do the wanted thing with a case sensitive column: ``` timestamp GLOB '2010-*' ``` etc.
(5) By Phil Endecott (endecotp) on 2021-08-09 12:14:33 in reply to 1 [link]
Thanks everyone. I have this working now. I was unaware that like is case-insensitive; in PostgreSQL there is a separate "ilike". As a further complication, I just realised that I had created the column with type "datetime" - which gives it integer affinity. I've now changed it to text.
(6) By Keith Medcalf (kmedcalf) on 2021-08-09 22:36:38 in reply to 5 [link]
A declared type of `datetime` gives that attribute NUMERIC affinity, not INTEGER affinity. See <https://sqlite.org/datatype3.html> in particular section 3.1 and the last row in the table at 3.1.1
(7) By Ryan Smith (cuz) on 2021-08-09 23:22:07 in reply to 5 [link]
What Keith said, plus the fact that NUMERIC is perfect for a column with date and time values and should work perfectly for your case. Also, in SQLite there is a [pragma "case_sensitive_like"](https://sqlite.org/pragma.html#pragma_case_sensitive_like) if you fancy changing that behaviour.