SQL query in Databricks to return the value of subtraction of time?

Question

From the following table, my goal is to fill in the value in the column "clmnTimeDiff". Suppose that I have clmnTime1 and clmnTime2, which store the value of "TIME" in string, I want to let the field clmnTimeDiff store the difference between clmnTime2 and clmnTime1. From this table, in the record ID = 1, the result in clmnTimeDiff should be 23:14:31 - 12:15:47 = hh:mm:ss. Actually, the full table contains clmnDate1 and clmnDate2 as well. Previously, I thought that I can follow this query:

SELECT 
      ID
     ,clmnTime1
     ,clmnTime2
     ,CONCAT(TIMESTAMPDIFF(HOUR, CAST(CONCAT(clmnDate2, ' ', clmnTime2) AS TIMESTAMP), 
      CAST(CONCAT(clmnDate1, ' ', clmnTime1) AS TIMESTAMP)),
      ':'
     ,TIMESTAMPDIFF(MINUTE, CAST(CONCAT(clmnDate2, ' ', clmnTime2) AS TIMESTAMP), 
      CAST(CONCAT(clmnDate1, ' ', clmnTime1) AS TIMESTAMP)),
      ':'
     ,TIMESTAMPDIFF(SECOND, CAST(CONCAT(clmnDate2, ' ', clmnTime2) AS TIMESTAMP), 
      CAST(CONCAT(clmnDate1, ' ', clmnTime1) AS TIMESTAMP))) AS clmnTimeDiff
FROM example_table

The problem is that my Databricks does not support TIMESTAMPDIFF(), so I'm running out of idea to solve this problem. How can I get the value in clmnTimeDiff?

Answer 1

Not 100% clear on what you are going for, but here is a guess

Example

Declare @YourTable Table ([clmnTime1] varchar(50),[clmnTime2] varchar(50))  
Insert Into @YourTable Values 
 ('12:15:47','23:14:31')
 
Select *
      ,NewVal = convert(time,dateadd(second,datediff(second,try_convert(time,clmnTime1),try_convert(time,clmnTime2) ),0))
 from @YourTable

Results

clmnTime1   clmnTime2   NewVal
12:15:47    23:14:31    10:58:44