How to Aggregate DateTime Differences

JedP

Scenario

Transactional product tables often have multiple date columns that need to be compared and aggregated. Many reports will require the average time between dates to track the overall progress, for example a customer transaction table might have the following date fields:

·       “purchase_date”

·       “ship_date”

·       “delivery_date"

Understanding the time between DateTime columns and aggregating those times to a total is a common way to track the progress, but by design, DateTime differences will result in a DateTime filed and not a calculatable integer.

Beast Mode Sample Code:

This code works on the DomoStats Activity Log dataset and is subtracting the Batch date from the Event date.

Beast Mode Sample Code Results:

The results are displayed in Hour:Minute:Second:Millisecond, but the column is still a DateTime field and not a calculatable integer.

Solution

1.  Convert the DateTime columns to seconds. This changes the datatype to integer so it can then be calculated.
2. Perform the calculation on the integer.
3.  Convert the seconds integer back to a DateTime datatype.

Beast Mode Example:

Here are a few Beast Mode code examples that use the above steps. These Beast Mode codes work with the DomoStats Activity log dataset.

Total Event Time in Seconds:

First, convert the DateTime to seconds and sum the results.

I am using the UNIX_TIMESTAMP and FROM_UNIX functions for their speed and simplicity. The TIME_TO_SEC and SEC_TO_TIME are popular options as well, but might need additional functions to get the format as needed.

SUM(UNIX_TIMESTAMP(`Event_Time`))

Time difference in Seconds:

Repeat the steps for each DateTime field and preform the calculation.

The "Time Difference in Seconds" is the Numerator for the "Average Time per Event" calculation.

SUM(UNIX_TIMESTAMP(`_BATCH_LAST_RUN_`)) - SUM(UNIX_TIMESTAMP(`Event_Time`))

Total Events:

The "Total Events" count is the Denominator for the "Average Time per Event" calculation.

COUNT(`Event_Time`)

Average Time per Event:

This function is set to format the time in Day:Hour:Minute:Second.

FROM_UNIXTIME((SUM(UNIX_TIMESTAMP(`_BATCH_LAST_RUN_`)) - SUM(UNIX_TIMESTAMP(`Event_Time`))) / COUNT(`Event_Time`), '%d:%h:%i:%s')

Outcome & Observations
Using this method you can now calculate the difference between DateTime columns and aggregate those times up to a total.

ST_-Superman-_

@JedP - this is great. Thanks for sharing.

@AnnaYardley - this seems like the kind of post that might make a better blog post then a Dojo topic as it really isn't a question, just a well thought out solution to a common problem.