Type 'time' Should Be Used with Dimension Groups Only


(Morgan Imel) #1

As of 3.54, Looker will produce a warning when a dimension or filter field uses type: time.
##When can I use type: time?
In Looker, the time type is reserved specifically for dimension groups. Using type: time along with timeframes allows a user to easily create multiple dimensions, each represented in its own timeframe such as time, date, month, etc. For example, if I create the following dimension group, I will have essentially created 3 dimensions that represent my data in different formats:

Old LookML
- dimension_group: created
  type: time
  timeframes: [ date, year, day_of_week ]
  sql: ${TABLE}.created_date

New LookML
dimension_group: created {
  type: time
  timeframes: [date, year, day_of_week]
  sql: ${TABLE}.created_date ;;
}

Which will end up looking like this in the result set:

To learn more about dimension_groups, check out the introduction or the reference page.

##When can’t I use type: time?
Though it’s tempting to use type: time and timeframes on a dimension or filter field, these parameters are reserved for dimension groups only. As of 3.54, Looker will produce the following warning if type: time is used on a dimension or filter field:

Additionally, type: time cannot be used with filter fields. If used this way, the following error will appear:

What type should I use instead?

If you only want to create one dimension with a single timeframe, you can specify which timeframe you want to use in the type. For example, acceptable dimension types are of the form date_<timeframe>, such as date_date, date_time, date_year etc.

Acceptable types for a filter field are string, date, and datetime.

The moral of the story

If you want to create more than one dimension, use a dimension group! Then you can use type: time and choose all the timeframes that your heart desires. If you only want a single dimension, you’ll need to tell Looker which timeframe you want. To do that, you’ll use type: date_<timeframe>.


Looker 3.54 Release Notes
(Levi Davis) #2

For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.


(Jesse St Charles) #3

Interestingly enough it appears the type time works as a type of measure, and produces a pretty handy group of measures at every time granularity. This appears to be undocumented but functional behavior. What was the intent here?

e.g.

  dimension_group: recorded_at {
    type: time
    timeframes: [time, hour, date, week, month, quarter, year, day_of_week]
    sql: ${events}:recorded_at::timestamp ;;
  }

  measure: earliest_recorded_at {
    type: time
    sql: min(${recorded_at_time})::timestamp ;;
    convert_tz: no
  }

(Morgan Imel) #4

Hi @Jesse_St_Charles,

We’re currently working on establishing what should/ shouldn’t work when it comes to undocumented measure types. Once we determine this we will definitely update our documentation appropriately!


(Paul Fernandez) #5

FYI, I found that measures of type: time generate an “unknown field” LookML warning when used in a set.