This topic outlines the purpose and uses of the
Date Difference calculated column available in the Manage Data > Data Warehouse page. Below is an explanation of what it does, followed by an example, and the mechanics of creating it.
Date Difference column type calculates the time between two events belonging to a single record, based on the event timestamps. The raw value calculated in this column is in seconds, but it auto-converts to minutes, hours, days, and so on, for display on reports. When used as a filter/group by, however, you want to use the value in seconds.
date difference calculated column can be used to create a metric which calculates the average or median time between two events, such as average time between customer registration and their first orders.
||2015-01-01 00:00:00||2015-01-01 12:30:00||45000|
||2015-01-01 08:00:00||2015-01-01 10:00:00||7200|
In the above example, the
Date Difference column is the
Seconds between timestamp_2 and timestamp_1 column. It performs the calculation
timestamp_2 minus timestamp_1.
The following steps describe how to create a
Date Difference column.
Navigate to the Manage Data > Data Warehouse page.
Navigate to the table on which you want to create this column.
Click Create a Column and configure your column as follows:
Column Definition Type>
Column Definition Equation>
DATE_DIFF = (Ending DATETIME - Starting DATETIME)
Ending DATETIMEcolumn > Choose the ending datetime field, which is typically the event that occurs later
Starting DATETIMEcolumn** > Choose the starting datetime field, which is typically the event that occurs earlier
Provide a name to the column and click Save.
The column is available to use immediately.
As an example, the following example is configured to calculate the
Seconds between order date and customer's creation date: