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.
Looker’s Table Calculations offer a simple way to compute the difference between two dates on the fly. The diff_days family of functions takes in two dates and returns the difference 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 to compute the number of days that elapsed between purchase and return:
Example 2: Age
Each of the users listed below has a signup date. We can use a table calculation to find out how many months have elapsed since the users signup, using the
now() table calculation:
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. I can use the
date table calculation to compare the signup date against a hard-coded date:
If you are interested in how to do this in LookML and want to build a difference of dates into the LookML model, so it can be grouped on and selected by other users, read our Computing the Difference between Dates (for Developers) article.