Postgres column type "timestamp without time zone" not working in filter widget

Get Help
1

Hello,

I try to create a variable and add date filter widget, here is the native SQL query as question:

select dbtime,count(*) from cdr where dbtime >= {{start_date}} group by dbtime;

Note: dbtime column in cdr table (postgreSQL) has data type as “timestamp without time zone”

And I have tried to configure “filed filter” , map variable “start_date” to table “cdr” column dbtime, select Field widget type to be “single date” with default date as “2020-03-01”, but I get below error when run query:

ERROR: operator does not exist: boolean = date Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 225

I’m not sure it’s a bug or not, hope someone has hint.

Thanks!

2

Hi @quinn
Which version of Metabase? Please post “Diagnostic Info” from Admin > Troubleshooting.
If you use Field Filter, then do not include the column in the query - it should just be:
select dbtime,count(*) from cdr where {{start_date}} group by dbtime;

1 Like
3

@flamber, thank you! you are right, after I do “where {{ start_date}}” , the error is gone.
However, I noticed the it took a long time to “do science”, if I use native SQL without variable, like "where dbtime = ‘2020-03-01’, the result is instant.
but when I use “where {{ start_date}}” , it took a long time to return result.
Any clue?

4

@quinn
Which version of Metabase? Please post “Diagnostic Info” from Admin > Troubleshooting.
I’m guess that you’re perhaps using a relative date in the filter, which means that it will not use indexes - there’s an issue open about that:
https://github.com/metabase/metabase/issues/11837 - upvote by clicking :+1: on the first post

Just for reference for others having problems with Field Filters: https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type

5

@flamber

version v0.34.3, docker installation

{
“browser-info”: {
“language”: “en-US”,
“platform”: “Linux x86_64”,
“userAgent”: “Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.88 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.5+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.5”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.5+10”,
“os.name”: “Linux”,
“os.version”: “4.9.0-8-amd64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“h2”,
“postgres”
],
“hosting-env”: “unknown”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “9.4.10”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-02-25”,
“tag”: “v0.34.3”,
“branch”: “release-0.34.x”,
“hash”: “1a83edb”
},
“settings”: {
“report-timezone”: null
}
}
}

6

But how to make index work? It seems to be an old issue of metabase

I'm surprised that it's still not solved, it's the blocking point for me to use metabase.

7

I have altered the table column “dbtime” from type “timestamp without time zone” to “date” and re-created index.

Then in metabase, I delete cache and re-scan field value.

Now it seems the query is using index, and get result much faster than before.

So it appears to me metadata has problem to deal with “timestamp” data type, it only works for “date”