Search code examples
oracle-databaseoracle-apexapexcharts

Create chart in APEX and add the average function considering hours, minutes and seconds

This is connected to the question here: Oracle APEX before insert trigger issue

I have the following information:

enter image description here

I would like to create a Chart in APEX using hours, minutes and second. Right now, I can just select one field (minutes), but how can I include the field hours on the average function? See the attached image. In the Time Taken part is just considering the minutes, but not the hours.

enter image description here

Solution

  • In the query, sum up the columns containing seconds/minutes/hours/days. So, suppose you want to see the data in minutes then you'd do

    SELECT 
  NVL(minutes,0) + NVL(hours,0)*60 + NVL(days,0)*24*60) AS total_minutes,
  ...
  FROM
  yourtable

    Then in the chart, take the total_minutes alias as your column.

    -updated-

    with the query from your comments, this would become:

    select id,
       startdate,
       enddate,
       extract(day from timetaken)    days,
       extract(hour from timetaken)   hours,
       extract(minute from timetaken) minutes,
       extract(second from timetaken) seconds,
       NVL(extract(minute from timetaken),0) + NVL(extract(hour from timetaken),0)*60 + NVL(extract(day from timetaken),0)*24*60 AS total_minutes
  from tests