# DP22 - Using Beast Mode to Build Data Storytelling - Formatted Time Duration Coach 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: