I have a “order” table with “Time” field, containing a unix timestamp (seconds resolution) of each order. This field is indexed.
In the Database modeler I’ve set the field type to “Unix timestamp (seconds)”, so that the Query builder recognizes it as date and allows me to choose a date interval as a filter.
However, as I’ve discovered, such filter is unfortunately translated into the following “where” expression:
where from_unixtime(Time) between '2019-02-01' and '2019-02-19'
This way MySQL cannot use the index on the “Time” field, because there is a function “from_unixtime()”
applied to it.
Is there a way to use this index?
One way to do it would be to write the filter with the following expression:
where time between UNIX_TIMESTAMP('2019-02-01') and UNIX_TIMESTAMP('2019-02-19')