Indexes on Date fields not used in some cases

I have this index:

CREATE INDEX `global_myDateIdx`
ON `models`(`realDate`)
USING GSI WITH {"defer_build":true}

And I’m running some queries that can look like this:

select * from models
where STR_TO_MILLIS(realDate) <= STR_TO_MILLIS(userInputtedDate)

Which produces an EXPLAIN execution plan, which does not use the index but does a primary scan. (Couchbase 4.6.0)

The date format stored within the field looks like this:


Always guaranteed to be in that format, always guaranteed to be UTC.

My question is, how should date range queries be done so as to exploit the indexes? Should I be arranging data so that the string comparison and the date comparison are the same? I have noticed that if I don’t use the STR_TO_MILLIS function that the index will be used in the query plan

Now need to convert to MILLIS. Date stored in ISO 8601 are string comparable.

SELECT * FROM models WHERE realDate <= "2017-05-24T14:47:37.951-07:00";