How do I remove the "0-0 0" from this time difference column?

Question

I have a data set that pulls the difference of time between each entered and deleted order. However with this code I get an output that reads 0-0 0 and the time difference. I would like to remove the 0-0 0 if possible.

SELECT CAST(Left(time, 8) AS time) AS time, 
       coalesce(buyer_order_id, SELLER_ORDER_ID) AS order_id,
       CAST(left(time, 8) AS time) - lag(CAST(Left(time, 8) AS time), 1) 
        OVER (partition by coalesce(buyer_order_id, SELLER_ORDER_ID) order by time) AS time_difference
FROM orders.sheet1 
WHERE message_type = "ENTER" OR message_Type = "DELETE" 
order by order_id

sample output:

time     order_id  message_type time_difference
01:56:26 267       ENTER        null
04:20:24 267       DELETE       0-0 0 2:23:58   
01:57:00 268       ENTER        null
03:31:57 268       DELETE       0-0 0 1:34:57

tried many variations of replace and right functions but have been unsuccessful since the output is a time one and not a string.

Answer 1

Would you try below ?

SELECT *,
       TIME (
         EXTRACT(HOUR FROM time_difference),
         EXTRACT(MINUTE FROM time_difference),
         EXTRACT(SECOND FROM time_difference)
       ) AS time_diff
  FROM (
    SELECT CAST(Left(time, 8) AS time) AS time, 
          coalesce(buyer_order_id, SELLER_ORDER_ID) AS order_id,
          CAST(left(time, 8) AS time) - lag(CAST(Left(time, 8) AS time), 1) 
            OVER (partition by coalesce(buyer_order_id, SELLER_ORDER_ID) order by time) AS time_difference
    FROM orders.sheet1 
  WHERE message_type = "ENTER" OR message_Type = "DELETE" 
) order by order_id;