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.
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:
**Did this solve your problem? Accept it as a solution!**
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 60 App Studio
- 41 Variables
- 687 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive