DP22 - Using Beast Mode to Build Data Storytelling - Formatted Time Duration
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.
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.
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
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 will add the different string values together to form a single value
**Did this solve your problem? Accept it as a solution!**
- 11.3K All Categories
- 6 Private Company Board
- 2 APAC User Group
- 12 Welcome
- 45 Domo News
- 10.2K Using Domo
- 2K Dataflows
- 2.6K Card Building
- 2.3K Ideas Exchange
- 1.2K Connectors
- 354 Workbench
- 271 Domo Best Practices
- 13 Domo Certification
- 482 Domo Developer
- 58 Domo Everywhere
- 112 Apps
- 734 New to Domo
- 86 Dojo
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 31 お知らせ
- 65 Kowaza
- 305 仲間に相談
- 667 ひらめき共有