Timestamp filters on indexes fields in MySQL


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')

Hi @dtroyan
This sounds like this open issue - go and vote by clicking the :+1: on the first post:
Or maybe this issue, which I believe is a duplicate of the above issue:

1 Like

Thank you, it is definitely the same issue!