Computing the Difference Between Dates (for Non-Developers)

[sam8]

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

If you have developer access, check out the documentation on using the type: duration parameter with a dimension group to easily calculate interval-based durations.

 

The Problem

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

The Solution

Looker's table calculations offer a simple way to compute the difference between two dates on the fly. The diff_days family of functions compares two dates and returns the difference between them as an integer.

Example 1: Difference Between Two Fields

Each of the orders in the table below has a created and returned date. We can use a table calculation with the diff_days function to compute the number of days that elapsed between purchase and return:

diff_days(${orders.created_date},${order_items.return_date})

 

Example 2: User Account Age

Each of the users listed below has a sign-up date. We can use a table calculation with the diff_months function to find out how many months have elapsed from when the users signed up (users.created_date) to today (which can be returned with now()😞

diff_months(${users.created_date},now())
 

 

Example 3: Time Between Events

Building off the previous example, let's assume the company was founded on November 30, 2011, and we want to find out how old the company was when each user signed up, in years. I can use the diff_years function, and the date function to compare the sign-up date (November 30, 2011) against a hard-coded date (users.created_date😞

diff_years(date(2011,11,30),${users.created_date})