I've got this query:

SELECT user_id, from_loc_id, to_loc_id, to_char(dstamp, 'hh24:mi:ss')
FROM inventory_transaction
WHERE code = 'Pick'
AND substr(work_group,1,6) = 'BRANCH'
AND dstamp BETWEEN to_date('24/02/2022  17:00:00', 'dd/mm/yyyy hh24:mi:ss') AND
    to_date('24/02/2022  18:00:00', 'dd/mm/yyyy hh24:mi:ss')
ORDER BY user_id;

That's the output:

My expected output is:

I was trying to use lag, but didn't really worked. I've just realized I need to add a second ORDER BY, so first by user, second by to_char(dstamp, 'hh24:mi:ss').

All solutions much appreciate. Thank you.

CodePudding user response：

You can use NUMTODSINTERVAL function with day argument and applying SUBSTR to extract hours:minutes:seconds portion as your data resides within a specific date such as

SELECT t.user_id, 
       t.dstamp,
       SUBSTR(
              NUMTODSINTERVAL(dstamp - LAG(dstamp) 
                        OVER (PARTITION BY user_id ORDER BY dstamp),'day'),
              12,8) AS time_diff
  FROM t

Demo

Edit : The case above is applied for the column dstamp is considered to be of date data type, if its data type is timestamp, then use the following query containing date cast instead

SELECT t.user_id, 
       t.dstamp,
       SUBSTR(
              NUMTODSINTERVAL(CAST(dstamp AS date) - LAG(CAST(dstamp AS date)) 
                        OVER (PARTITION BY user_id ORDER BY CAST(dstamp AS date)),'day'),
              12,8) AS time_diff
  FROM t

Demo