Using the raw timeframe (3.32+)

#1

As of Looker 3.32, we have introduced a raw timeframe for dimension groups of type: time. The raw timeframe has some unique attributes worth noting:

  • It will return the date field in the same format as in your database without any formatting or timezone conversions.
  • It is only accessible within LookML and will not appear in Explore.

The raw timeframe is meant to be used in particular for performing date operations on a field. It returns a timestamp, whereas most other timeframes return a formatted string.

Let’s say I have a timestamp in my database that looks like this:

In Looker, you can create a dimension_group for this timestamp with the raw timeframe like so:

 dimension_group: created {
  type: time
  timeframes: [date, raw]
  sql: ${TABLE}.created_at
}

I can then perform any date operation in LookML on this timeframe. For example, this dimension calculates the difference in days between now and the created date (in Redshift):

Note that no timezone conversions are performed for either of these dates.

 dimension: days_since_created {
  type: number
  sql: DATEDIFF(day, ${created_raw}, current_date)
}

Read more about taking the difference between dates here.

1 Like

LookML Best Practices
How to perform date operations on a Looker timeframe
(Daniel Demetri) #2

This is very exciting

0 Likes

(Willemijn) #3

Hi Lindsey,
Am I correct that the introduction of this raw date-format means that the “need or desire to write raw SQL” on http://www.looker.com/docs/reference/explore-params/required_joins is outdated now?
Maybe nice to link to this article from that page?
Cheers, Willemijn.

0 Likes

(Ken Cunanan) #4

Nice catch Willemijn! We’ll be sure to update our docs accordingly to account for the fact that the raw timeframe negates the need to join on raw sql in that specific instance.

Best,
Ken

0 Likes