How to pass time filters to SQL Lab queries

[cachafla]

Is there a way to pass [from, to] date filters to an SQL query?

I've been looking everywhere in documentation and source code and haven't found a solution to passing date filters to custom SQL queries. I'm working on some advanced multi-join aggregations from existing tables/views.

As a very simple example of the issue I'm trying solve, consider this SQL query:

SELECT COUNT(*)
FROM sessions
WHERE timestamp <= {{ something }} AND timestamp >= {{ something_else }}

I'd like to be able to access the date filters that are propagated through a Filter Box widget. in a dashboard. I've found that the values of these filters are available as these variables (thanks to https://superset.incubator.apache.org/faq.html#how-to-add-dynamic-filters-to-a-dashboard): https://github.com/apache/incubator-superset/blob/40776bd547166b6975574e815ec02add04781248/superset/assets/src/visualizations/FilterBox/FilterBox.jsx#L38-L44

With that in mind, I could make use of __time_range inside my SQL filter via filter_values('__time_range'), but __time_range is always giving me the literal time range selected, not a parsed [from, to] couple of date objects. I get values such as:

• 'Last quarter'
• '2019-09-02T00:00:00 : 2019-09-03T00:00:00'

These variations are very difficult to deal with inside an SQL query.

I'd like to know if this is all I get or if there's an undocumented solution to my problem I don't know about. In its simplest from, I'd like to have filters available in the SQL as template params so that I can do things like:

timestamp >= {{ from }} AND timestamp <= {{ to }}

[lilila]

Is there any update on this topic? We also tried to get the values of time range filter using filter_values but with superset 1.0.rc4 we get no value (not even a string value like the one mentioned by @cachafla )

The problem we are facing is the following:
We have time series and we would like to plot them aligned (independently of the date). To do so, we compute a fake_date based on the difference in days between the first available date and the current date. Thus all figures for Day1, Day2... are aligned for the different lines. It works well but when we want to add a time filter, then it is applied to the fake date and it breaks the logic.

It would be nicer if :

• aligning time series based on the first available observation is an option of the plot.
• or to allow the line chart to take as x_axis integer (like day num) instead of date
• or the best would be to allow the user to get the information from the time range filter (as other filters values), in the case described above it would allow us to select data from the right period of time and then create dynamically a fake_date that would fit the time range selection.

More generally, why is the time range filter different from the other filters?

[caiobelfort]

I think the variables {{ from_dttm }} and {{ to_dttm }} can be used to retrieve a date range filter values.

[k7daniel]

But these values are not available while in SQL Lab or when verifying a datasets columns?

[kevin-brendle]

But these values are not available while in SQL Lab or when verifying a datasets columns?

in SQL Lab you just need to define them with

{% set from_dttm = '2022-01-11 23:00:00' %}
{% set to_dttm = '2022-01-11 23:10:00' %}

and then your query will work

[TheRum]

I am also facing the same issue in version 1.0.0 . It was working in prior versions but is broken now.

Here's the function I have defined in superset_config.py .

from superset.utils.date_parser import get_since_until
from typing import Optional, Any
from flask import request
import json

def time_filter(default: Optional[str] = None) -> Optional[Any]:
    form_data = request.form.get("form_data")

    if isinstance(form_data, str):
        form_data = json.loads(form_data)
        extra_filters = form_data.get("extra_filters") or {}
        time_range = [f["val"] for f in extra_filters if f["col"] == "__time_range"]
        time_range = time_range[0] if time_range else None

        since, until = get_since_until(time_range)
        time_format = '%Y-%m-%d %H:%M:%S'

        until = until.strftime(time_format)
        if not since:
            return '<= \'{}\''.format(until)
        since = since.strftime(time_format)
        return 'BETWEEN \'{}\' AND \'{}\''.format(since, until)
    return default

JINJA_CONTEXT_ADDONS = {
    'time_filter': time_filter
}

[qlpanalytics]

Thanks for checking into it, @TheRum! We are stumped, so keeping our fingers crossed that you're able to find some sort of workaround/solution :)

[TheRum]

@qlpanalytics I checked in the documentation and issues but nowhere it is mentioned whether it is supported or not. So, I believe templating is not supported in the calculated columns/metrics. I tried as well but no luck.
I think you should ask Preset team via email or slack, maybe they can confirm if it is supported or not. And if yes, how you can implement the same.

Hope this helps 🙂.

[qlpanalytics]

@TheRum thank you so much for the follow up! I will reach out to the team as suggested. If this turns out to be possible, I will update this thread with the solution. Thanks again!

[MaximSlagter]

@qlpanalytics Any updates? ;)

[qlpanalytics]

@MaximSlagter unfortunately this did not work and we were unable to complete the project as intended in Superset. We were successful in achieving this in Google Data Studio though - better than nothing :)