Starting in Looker 6.2, developers can use the duration type instead of the method in this article.

If you do not have developer access, check out the 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 these two dates. 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 SQL for this does vary by database. The LookML will look something like this:

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

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

Grouping together Multiple Dimensions

Let’s say we take this approach for several different interval types, such as day, month, and year. We can group these together in the Field Picker using the group_label function.

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( day, ${created_raw}, ${deleted_raw}) ;;
}

The end result looks like this: