Skip to Main Content
Feature Request FR-1157
Product Area Application Builder
Status CLOSED

9 Voters

Format mask for formatting duration columns (e.g. a number of seconds) in interactive reports/interactive grids.

tsch Public · Mar 29 2017

It's currently difficult to report on columns representing a duration (e.g. a number of seconds) in interactive reports/interactive grids when:

  • This data has to be displayed in an easily readable format (e.g. days hh:mi:ss).
  • You would like to continue to take as much as possible advantage of features like breaks, aggregates, ... available in IR/IG.

The duration data could be formatted by a user defined function in the report query. But in that case you can no longer perform actions like sum on that data in the IR/IG. Using INTERVAL DAY TO SECOND columns is also not a solution as the standard SUM() function in the database doesn't accept this dataatype as input.

The possibility to use an Oracle Discoverer (0HH:MI:SS format mask) or Excel ([h]:mm:ss format mask) like mechanism to format such columns would be a solution.

Oracle Discoverer will be moved out of support in a few months. The Application Express IR/IG could be considered as one of the alternatives to replace Oracle Discoverer. In a specific business case I found that Oracle Discoverer reports with durations using the 0HH:MI:SS format mask are currently difficult to re-implement in Oracle Application Express.

We reviewed this idea carefully, and while it was interesting, we concluded that due to all the internal implications we need to take into account, it is unlikely to make its way into APEX.

Comments

Comments

  • fac586 OP 3.7 years ago

    APEX reports support the SINCE format mask, returning 18 minutes ago, 5 days ago, 3 weeks ago etc, which is sufficient for some use cases.

    However this is really something that needs to be implemented in the database rather than in APEX. If Oracle supported aggregation and formatting of INTERVAL types then APEX would too…

    Feature requests in Database Ideas:

  • michael.weinberger 2 OP 2.3 years ago

    We would like to have this as an item: a way for the user to enter something like “ 1h 15m or 1.25 or 01:15 (or pick a value from a clock) ”  in the same item. The value should be  store as interval or date column.

  • menno.hoogendijk APEX Team OP 1.8 years ago

    Oracle 23c now supports SUM on INTERVAL.

    Release 23c adds support for INTERVAL interval data types. However interval data types cannot be implicitly converted to a numeric data type. If the input is an INTERVAL, the function returns an INTERVAL with the same units as the input.