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: