How do I speed up this strftime('%Y-%m', time_UTC) query?

Question

This query takes a long time:

--How I call for months:
SELECT DISTINCT strftime('%Y-%m', time_UTC) AS month FROM transacts ORDER BY month ASC;

How do I speed up this query for dates available?

Answer 1

You can create an index on the expression strftime('%Y-%m', time_UTC) to get better performance:

CREATE INDEX id_transacts_time ON transacts(strftime('%Y-%m', time_UTC));

Check in the demo the query plan that uses the index.