[GitHub] [incubator-superset] dengshaochun commented on issue #8183: How to pass time filters to SQL Lab queries

[GitBox <gi...@apache.org>]

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:
users@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org