Use Dashboard Filter on dates in Unix TimeStamp format

The database tables I use store dates in Unix TimeStamp, in bigInt format. Is there a way to use the Filters feature on this type of input data to make the selection over a certain period of time?

I accept suggestions on how to resolve this issue.

Hi @J_trida
Yes, set the Field Type in Admin > Data Model > (database) > (table) > (column) :gear:
https://www.metabase.com/docs/latest/administration-guide/03-metadata-editing.html

Thanks for the answer, but I don’t see how it can work. Even changing the metadata to values of type “date” my query does not show results.

Performing a native query, such as:

Since my table contains 3 dates for the filter value entered:

From the documentation, I understood that changing the type of metadata is only reflected in the visualization, making perfect sense. So, I come back with my question to check if the platform offers any resource that allows me to accomplish my goal (probably through code my goal is reached, but I want to shorten the path). Or, I may have done something wrong.

The “startData” column of the table is originally in the “bigInt” format (which corresponds to the date value in Unix), is there any resource that allows a parameter for date filtering to recognize this value?

@J_trida Okay, so you’re using SQL, then you need to do the conversion yourself. I don’t know which database you’re using, but for MySQL it would be something like:

select id, from_unixtime(startDate)
from arch_process_instance
where date(from_unixtime(startDate)) = {{data}}

https://stackoverflow.com/questions/6267564/convert-unix-timestamp-into-human-readable-date-using-mysql
You’ll likely find better help on stackoverflow.com or a forum dedicated to your database, since it’s not specific to Metabase.

1 Like