Datagroup doesn't support _query._query_timezone

done
low_priority
reply

(vfx) #1

The following definition produces an error:

datagroup: half_hourly {
  sql_trigger:
    SELECT
    case
      when EXTRACT(MINUTE FROM CURRENT_DATETIME("{{ _query._query_timezone }}")) BETWEEN 0 AND 29 THEN "00-29"
      else "30-59"
    end
  ;;
  max_cache_age: "30 minutes"
}

According to documentation _query._query_timezone is supported in all parameters starting with sql. It says:

S = Works with all LookML parameters that begin with sql (e.g. sql , sql_on , and sql_table_name )

Is it a bug?

Thanks


(Fabio) #2

Although arguably it should work, I’m wondering whether it would do what you expect it to do? Since the queries for datagroup/PDT triggers are run independently of any specific user, it would effectively just give you a static value per connection. Is that what you are trying to achieve?


(vfx) #3

I agree this particular SQL snippet (trigger on half-hour) is almost always independent from the timezone (unless it’s, say Nepal (GMT+5:45) for example). So I’m just giving to illustrate the liquid variable doesn’t work.

But if you want the data trigger to be on a round hour then for all timezones that are not aligned with GMT on a full hour, it’s not going to work.

Hope this explanations helps.


(Fabio) #4

What I’m trying to understand is on what the timezone should be dependent. Since trigger queries are not per-user queries, what purpose does the variable serve that a hard-coded timezone wouldn’t?


(vfx) #5

Ok, I get your point - in case of data trigger for PDT it’s not useful as it’s global and not user dependent.

Thanks for the clarification and sorry for the noise…