Integer timestamp request builder issue

Get Help
1

Hello,

I have problem with query builder in metabase.

I have database with timestamp field respresended as int(11). I turn on casting from UNIXSeconds to DateTime in admin pane but there is small performance issue with this.

  (
    FROM_UNIXTIME(`transaction`.`time`) >= DATE(DATE_ADD(NOW(6), INTERVAL -7 day))
  )
 
   AND (
    FROM_UNIXTIME(`transaction`.`time`) < CAST(DATE(NOW(6)) AS datetime)
  )

This query is really slow for bigger tables. It's casting timestamp to datetime and compare. Is possible (without creating custom SQL) create requests that compare date as timestamp?

I using view with range selector and it's works but is slow.

2

Can you do the cast on the other side of the condition? Case the Date to a unixtime rather than the other way round. That will be much more efficient.

3

@AndrewMBaines
This is query from query builder not created by me.

4

Sorry, missed that.
All I can think of is to create a calculated column on the database with index but that depends upon your rights and what you can do to the database.
Alternatively, a materialised view to get rid of the unix time altogether. Again, that depends upon the database.

5

Hello there,

Unfortunately we don't support operating on the column as integer directly from the query builder when it has casting enabled.
Indeed one workaround would be to do this through a view, or even to index this with a functional index like:
CREATE INDEX transaction_time_unixtime_idx ON transaction ((FROM_UNIXTIME(time)))
Which should use the index in the query builder's generated SQL