How to perform date operations on a Looker timeframe


#1

As of Looker 3.32, we have introduced the raw timeframe, which allows you to perform date operations on the underlying value of your date field without timezone conversions. Read more about this here.

Let’s say you have two date fields in different view, and you want to subtract those dates. In order to refer to a field from another view in Looker, you must use ${view_name.field_name} syntax. This becomes more complex with date fields, since Looker automatically casts timeframes from dimension groups into strings.

Note: the example in this article are in MySQL. Syntax may vary slightly in other SQL dialects. Read more about calculating the difference between two dates in other dialects.

For example, if you try to simply subtract two Looker timeframe fields like this:

- dimension: date_diff_example
  type: int
  sql: DATEDIFF(${user_order_facts.first_order_date}, ${users.created_date})

you may see an error along the lines of You may need to add explicit type casts.

Solution: Cast Looker fields back to dates

The solution here is to explicitly cast the Looker timeframe fields from strings back to dates. This will look something like:

- dimension: date_diff_example
  type: int
  sql: DATEDIFF(${user_order_facts.first_order_date}::date, ${users.created_date}::date)

This will work in most cases with casting back to date, datetime, or timestamp, depending on your use case.


Is there a way to do date offset using Calculations?
How to Create a Quarter Timeframe Dimension (prior to 3.34)