Mysql unixtimestamp date filter issue

we are using mysql and not able to use datefilter, used below workaround created one variable. If i want to use compare one date to another date not able to create another date control field.

and FROM_UNIXTIME(created_at)= {{created_at}}

as per one article i went to datamodel and try to select unitxtimestamp this option not showing .

Please help us on this matter

Hi @giribm
Please post “Diagnostic Info” from Admin > Troubleshooting.
What is the database column type of created_at?

created_at unixtimestamp example 1588521600

{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.105 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “Cp1252”,
“java.runtime.name”: “Java™ SE Runtime Environment”,
“java.runtime.version”: “14.0.2+12-46”,
“java.vendor”: “Oracle Corporation”,
“java.vendor.url”: “https://java.oracle.com/”,
“java.version”: “14.0.2”,
“java.vm.name”: “Java HotSpot™ 64-Bit Server VM”,
“java.vm.version”: “14.0.2+12-46”,
“os.name”: “Windows Server 2016”,
“os.version”: “10.0”,
“user.language”: “en”,
“user.timezone”: “Asia/Singapore”
},
“metabase-info”: {
“databases”: [
“h2”,
“mysql”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-07-21”,
“tag”: “v0.36.0”,
“branch”: “release-0.36.x”,
“hash”: “b31049f”
},
“settings”: {
“report-timezone”: null
}
}
}

@giribm There’s no column type called unixtimestamp on MySQL. There’s integer, timestamp, datetime, and many more.

Upgrade to 0.36.1, since it fixes some issues.

And you should migrate away from H2, if you’re using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html

@flamber thanks for reply, sorry for misunderstand. In our mysql created_at column type is text and in queries i am using FROM_UNIXTIME to convert and use for date filter. From datamodel can i change to date and time?

if i continue to use FROM_UNIXTIME how to create 2 date filter variables with same column in report?

image

@giribm Metabase cannot handle text for dates - all queries would be very slow. You should either use integer or even better a timestamp/datetime instead.

You would have to do something like this if you don’t convert the column:
and created_at = TO_UNIXTIMESTAMP({{created_at}})

To create additional variables, you add more {{another}}:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html