Note: A similar effect can be achieved with Table Calculations, which do not require Developer access. Check out our article on Computing the Difference between Dates (for non-Developers). 

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: