dengshaochun commented on issue #8183: How to pass time filters to SQL Lab queries URL: https://github.com/apache/incubator-superset/issues/8183#issuecomment-558443882 > @GiannisDimitriou Sure. > > ``` > from superset.utils import core as utils > ... > > 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 = utils.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 > } > ``` > > This is what I do, and it works for me. I do make some assumptions (taking the first time range filter if there are multiple, for example). > > @villebro & other maintainers: do we want this? I can open a PR with `time_filter` as a default Jinja function if we do. In recent releases, the form_data structure has changed v0.35, `time_range = form_data.get("time_range")` ```python # jinja_context.py from superset.utils import core as utils # .... def time_filter( time_format: str = '%Y-%m-%d %H:%M:%S', cast: str = 'datetime', engine: str = 'default', default: Optional[str] = None) -> Optional[Any]: """ get time filter from form_data :param time_format: datetime format string :param cast: database cast function, like mysql "cast('20191111' as SIGNED)" :param engine: database engine :param default: default value to return if there's no data :return: returns a date string or None """ form_data = request.form.get("form_data") from string import Template # todo other database _dict = { 'mysql': Template('CAST(\'${value}\' AS ${cast})'), 'default': Template('\'${value}\'') } t = _dict.get(engine) 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 time_range = form_data.get("time_range") since, until = utils.get_since_until(time_range) until = until.strftime(time_format) t_until = t.substitute(value=until, cast=cast) if not since: return '<= {} '.format(t_until) t_since = t.substitute(value=since.strftime(time_format), cast=cast) return 'BETWEEN {} AND {} '.format(t_since, t_until) return default # config.py JINJA_CONTEXT_ADDONS = {"time_filter": time_filter} ``` test original sql ```sql select * from xxx WHERE data_day {{ time_filter('%Y%m%d', 'SIGNED', 'mysql')}} ``` jinja2 formatted like ```sql select * from xxx WHERE data_day BETWEEN CAST('20191122' AS SIGNED) AND CAST('20191124' AS SIGNED) ```
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
