As part of my work on the UI for defining calculations, I have considered the role of time offsets such as month over month as it applies to our visualizations (including visualizations that don't support layers). Specifically, I propose that the Lens editor can add additional columns with a time offset to any visualization that supports time. To give a simple example, let's consider the Top 3 categories this week query, which returns:

Now I want to compare these numbers to the previous week, so I click the "Add time offset" button, and say "1 month". The final table is now:

What the Lens editor has done is create a new datasource layer with a different context, and executed what is commonly called a FULL OUTER JOIN in SQL terminology. The visualization doesn't need to know where the data came from, it just needs to be provided with a set of column definitions.

Doing time shifting on date histograms

One of the common use cases for this is to use time shifting to compare something like "today at 12pm" to "one week ago at 12pm". The FULL OUTER JOIN wouldn't match the dates in the date histogram. The simplest approach is to use a library like moment to add the time interval to each date field in the results. It's about as good as we can do, but there might still be some degenerate cases like leap days or daylight saving time. A good example is:

Which time offsets should we support?

I would propose that we keep this to a simple list of mostly easy-to-divide intervals

• 1 hour
• 1 day
• 1 week
• 4 weeks
• 1 month
• 1 year

Dependencies

Esaggs: #71287