Oracle Query (Time)

Question

I was wondering if somebody could help me with this query. I am trying calculate the minutes for the "Hour" column so that way I can know how many minutes it took an operator to complete a task. If it possible also if there is a way to add all those minutes per User_ID and divide them by the sum of the cases picked and the sum of lines picked. I hope anybody can help me with this.

This is the query I currently have:

--Productivity "Cases/Lines Picked"
select trunc(dstamp) "DATE", user_id, to_char(dstamp, 'HH12') "HOUR", to_char(dstamp, 'AM') "AM/PM",
ROUND(sum(update_qty / substr(sku_id, instr(sku_id, '-', 1, 1) +1 , instr(sku_id, '-', 1, 2) - 1 - instr(sku_id, '-', 1, 1)))) "CASES_PICKED",
COUNT(CODE) "Lines Picked"
from v_inventory_transaction 
where client_id = 'USKIDS2CA' 
and code = 'Pick' 
and list_id IS NOT NULL 
and STATION_ID LIKE 'R%' 
and reference_id not like '%-FK%' 
AND trunc(dstamp) = '03/21/2022'
group by trunc(dstamp), user_id, to_char(dstamp, 'HH12'), to_char(dstamp, 'AM')
ORDER BY 4;

enter image description here

Answer 1

If you're using an DATE column there is no need to calculate the minutes you can extract them

  ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';


SELECT TO_CHAR(SYSDATE,'HH24') AS CURRENT_HOUR FROM DUAL;

SELECT TO_CHAR(SYSDATE,'MI') AS CURRENT_MIN FROM DUAL;