How to display time split in days, hours, minutes and seconds?
Let’s say we have a Duration Time column which contains a number of seconds of duration. We would like to display it in the report as value divided into days, minutes, hours and seconds.
For example to display 20,000 seconds is 5 hours, 30 minutes and 15 seconds.
We can do that in Answers using one of two methods:
Setting Custom data Format for a column
OBIEE offers setting custom column data format masks, including one to convert numbers in seconds, minutes, hours or days into DD:HH:MM:SS format.
How to set column’s format to display duration
- Go to Column’s properties
- Go to Data Format and check Override Default Data Format
- Select Treat Numbers As Custom and enter Custom Numeric Format from one of the below strings:
[duration(sec)] – this part defines whether the base value is stored in seconds (sec), minutes (min), hours (hour) or days (day).
dd:hh:mm:ss – output format in days, hours, minutes in seconds. We can add [opt:] before each part to tell OBIEE to omit this value if the opt part has no value (for example the value is less than a day, then the day is omitted).
| Data Conversion Format String | Output |
| [duration(sec)][opt:dd]:hh:mm:ss | Formats the total of seconds as duration. For example, a duration value of 16500.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds.opt:dd displays the number of days, but if opt has no value, it is not displayed.
Second is the default unit of time. |
| [duration(min)][opt:dd]:hh:mm:ss | Formats the total of minutes as duration. For example, a duration value of 275.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds. |
| [duration(hour)][opt:dd]:hh:mm:ss | Formats the total of hours as duration. |
| [duration(day)][opt:dd]:hh:mm:ss | Formats the total of days as duration. |
The drawback of this solution is that it would display the values separated by colon, without giving the descriptions (for example 2 days 6 hours 32 minutes). If we need to add descriptions, we would need to use the other solution. Another issue may appear when exporting the report with custom data formatting to Excel, this bug is fixed since version 11.1.1.7.150120.
For reference, check Formatting Analyses, Views, and Dashboard Pages in OBIEE documentation at https://docs.oracle.com/middleware/1221/biee/BIEUG/format.htm#BIEUG1172 .
Splitting it inside the column’s formula
We can add a column formula to the column with values in seconds to split it by days, hours, minutes and seconds. That way we also can add some text.
First we need a column that contains a numeric value expressed in seconds, minutes, hours or days. We can have it already calculated in the source database or through ETL process.
If we don’t have the value calculated we can use another OBIEE function: TimestampDiff to calculate difference between two dates expressed in seconds, minutes, hours or days depending on the interval we specify.