Use Variable for text portion of date_trunc

1

I want to allow the viewer of my question to filter the timeframes in which rows are counted. Specifically, I want to be able to set the text portion of the date_trunc command as a variable.

A simplified version of my query looks like:
SELECT date_trunc(’{{timeframe}}’, created_at), COUNT(*)
FROM meetings
WHERE created_at > {{start_date}}
GROUP BY 1

The ‘start_date’ variable is required to be a date and works fine. The query throws an error when I try to include the ‘timeframe’ variable (required to be text and set default to ‘day’). In this specific case, the error I’m getting is “The column index is out of range: 2, number of columns: 1.”

I know that when you build a custom question and count rows over certain timeframes, Metabase by default creates a widget at the bottom allowing you to switch the timeframe you are truncating to. Is this possible with native SQL? If so, what can I do to correct the query above?

2

I believe your query is correct, but you do not need to wrap the {{timeframe}} in single quotes. By default, if you set the variable type in Metabase to “Text” Metabase should automatically wrap the data in single quotes when you reference it in the Native SQL query. So I think the following query should work:

SELECT date_trunc({{timeframe}}, created_at), COUNT(*) FROM meetings WHERE created_at > {{start_date}} GROUP BY 1