As of Looker 6.2, developers can use the type: duration parameter with a dimension group instead of the method described in this article.

If you do not have developer access, check out the article on Computing the Difference between Dates (for non-developers) for a method that uses table calculations to achieve a similar effect.

The Problem

I have two dates and I'd like to know how much time has elapsed between them. I might want to know this result in days, months, or years.

The Solution

This can be written into LookML as a dimension of type: number. The LookML will look something like this:

Note: The SQL in this example may need to be updated in accordance with your database SQL dialect.

dimension: date_diff {
  type: number
  sql: DATEDIFF( day, ${created_raw}, ${deleted_raw}) ;;
}

Notice the use of the raw timeframe. This timeframe returns the timestamp from the database, as opposed to other timeframes, which are cast to strings using SQL to format the date.

Grouping together Multiple Dimensions

For example, we take this approach for several different time interval types, such as day, month, and year. We can group these together in the field picker using the group_label parameter, like so:

dimension: date_diff_day {
  type: number
  label: "By Day"
  group_label: "Difference"
  sql: DATEDIFF( day, ${created_raw}, ${deleted_raw}) ;;
}

dimension: date_diff_month {
  type: number
  label: "By Month"
  group_label: "Difference"
  sql: DATEDIFF( month, ${created_raw}, ${deleted_raw}) ;;
}

dimension: date_diff_year {
  type: number
  label: "By Year"
  group_label: "Difference"
  sql: DATEDIFF( year, ${created_raw}, ${deleted_raw}) ;;
}

The end result looks like this: