Hey !
I want to use a date range on one of my cards in my dashboard.
So, I have a query with a field filter linked to a field in my database that’s a Unix timestamp. Because of that, I had to modify the field’s semantic type in my table’s metadata so that Metabase could recognize it as a date, and I set it to “Creation timestamp.”
By doing this, I was indeed able to link my dashboard’s date range filter to my card (which wasn’t possible before). First question : is there any way to use a unix timestamp with my field filter for the date range and not changing the metadata ? Problem: when I apply the date range filter to the dashboard, I get the error “There was a problem displaying this chart” on my card.
When I click on the card after applying this filter in the dashboard :
Create a virtual column in the database or Model column that converts the UNIX timestamp into a datetime. It needs to be in a form that Metabase can use in a WHERE clause.
Putting the converted column in the database will let you put an index on it, which will give good performance for range queries.
I have PostgreSQL tables written to by external tools that send UNIX timestamps. I put a generated/stored column in with a function that converts the UNIX timestamp to a timestamptz, then indexed the generated column.
Thanks for the answer. Is that the only solution? I potentially have hundreds of databases where I’d have to do that, and I’m not sure I’m authorized to change the database.
You could write all your questions using native queries and use database functions to convert the variables to UNIX timestamps (i.e., on postgresql, EXTRACT(EPOCH from {{variable}})), but it won’t work on date ranges (you’ll have to make separate filters for begin and end time) and block using the notebook editor and the drill-down feature.
MySQL 8.0 and later supports virtual columns and indexes on virtual columns. See if your DBA can add a virtual column that converts the timestamp to a datetime, and put an index on it. They will probably have to write a function to do the conversion as MySQL will not allow datetime’s to be directly handled in the generated expression due to interactions with the user timezone setting.
Another way might be to use a proxy database, like DuckDB or Druid, to query the MySQL database and add the converted column.
