We are using Big Query and are able to get most of our queries working. However, it doesn’t seem to work when we use a date filter variable. When I execute the following code, I get an error that reads “Unrecognized name: production at [8:16]”.
#standardSQL
select
timestamp
from
production.production_20180221 table
where
{{date}}
limit
10
Does the date filter not work with sql?
Hi,
I don´t use Big Query but a SQL database, therefore not sure if that is exactly comparable …
Have you tried using a field filter that is linked to the date field in your database?
I just realised that I use a lot of filters for dates, but never a date filter, I always link the field filter to a date field.
Let me know if it works!
Eva
Similar issue here. Time and date filters won’t work when applied to hand-written SQL questions.
That’s a shame. Do you know if there is a githhub issue for this?
Yes, this issue is very limiting.
I get a different error from you though.
When I set a 'WHERE {{Time_range}}' filter I get the error:
ERROR: cannot cast type integer to date Position: 417
even though I set the time column to UNIX timestamp.
Here is my ticket: Time filter in native SQL query throws 'cannot cast' error · Issue #7020 · metabase/metabase · GitHub
Not using BigQuery here - still just a “maybe useful” note:
I think I saw some mention in BigQuery related GitHub issues that there’s a distinction between “new style” (was it called Standard SQL?) and old/legacy style syntax for BigQuery - and also that the current Metabase driver is still on the old syntax or API. So maybe search for these terms on GitHub and then amongst you BigQuery guys share the high level overview here in the forum?
Note we’re exprimenting with building a few WIKI: marked Topics in the FAQ category in here. You’re more than welcome to start adding a BigQuery pointer from these topics where you see appropriate as well (as the first posts in those topics are Wiki pages - for everyone to edit)
@jornh In the query builder, if you add “#standardSQL” to the first line, it will use standard sql.
@bugo99 My data is set to a timestamp in bigquery and is labeled as “type/DateTime” in metabase. I think your best bet is to make sure it is already cast in bigquery rather than having to cast it in metabase. I actually don’t even have the unix timestamp option in my metabase?
By the way, I can get my query to work when I use the sample dataset which is using h2. This is why I’m thinking it is an issue with bigquery and not an issue with writing sql and using the date variable.
@jkindness This is interesting, I see no datatime, only UNIX Timestamp (v 0.28, the latests). Will try adding a datetime column and see if this type shows up. Will also try to play with the sample datase. Metabase is really an amazing tool should just be slightly more documented.
@bugo99 It sounds like we have different issues. If you get passed your type cast issue, let me know if you encounter the same error as me.
For now I can confirm that I am experiencing the same issue as @yugoli as outlined in his post:
Unfortunately he has not received a response either.
Hi all,
One of our engineers took a look and determined that the standard sql support for filters isn’t quite there yet. In the interim, we are making due by using text filters. So a user would type in 2018-01-01 in the dashboard filter and it will work in standard sql.
Cheers,
Li