DP22 - Using Beast Mode to Build Data Storytelling - Formatted Time Duration

GrantSmith

This post is part of a series of beast modes I presented on during my “Using Beast Mode to Build Data Storytelling” Domopalooza 2022 session. If you find this post helpful please make sure to give it a like or an awesome below.

Use Case

We wanted a way to display how long an invoice took to process in a more human readable format. Instead of displaying the duration in seconds or hours and having the user do the “mental math” to convert it themselves this beast mode does that for the user to allow them to focus more on the story the data was telling.

Beast Mode

CONCAT(
CASE WHEN `Ship Time` <= `Order Time` THEN '-' ELSE '' END,
-- Days
FLOOR ( ABS ( UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`) ) / 86400 ),
'd ',
-- Hours, Minutes and Seconds
SEC_TO_TIME ( ABS ( MOD ( UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`), 86400 ) ) )
)

Beast Mode Breakdown

CASE WHEN `Ship Time` <= `Order Time` THEN '-' ELSE '' END

If the ship time is before the order time precede the result with a negative. This allows us to simplify the logic calculating the difference and to have the negative displayed at the beginning of the number days instead of in the middle at the beginning of the hours.

UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`)

UNIX_TIMESTAMP returns the number of seconds (epoch) since January 1st 1970

Subtracting two UNIX_TIMESTAMP values calculates the difference between two timestamps in seconds

ABS ( UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`) ) / 86400

ABS will return the absolute value of the difference between the two times. This will force a negative difference to be positive. Again this allows us to simplify the logic and display the negative symbol one time instead of before the days and before the hours:minutes:seconds.

Dividing the number of seconds by 86400 (number of seconds in a day / 24*60*60) gets the number of days

FLOOR ( ABS ( UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`) ) / 86400 )

FLOOR drops anything after the decimal point returning whole days in the duration. We’ll be converting the partial days in the next step to hours, minutes and seconds.

SEC_TO_TIME ( ABS ( MOD ( UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`), 86400 ) ) )

MOD function will divide the duration in seconds by the number of seconds in a day (86400) and return the remainder. This will be the value of the partial day in seconds.

SEC_TO_TIME will take the remaining seconds and format it as HH:MM:SS

CONCAT(…)

CONCAT will add the different string values together to form a single value

Final Result: