Elapsed Time From two different time in sql

Question

I have a table that content starttime and completiontime i would like to get the elapsed time for this data i have made a query base on it. but it is not working correctly.

starttime: 2020-09-04 15:21:34.703333 completiontime: 2020-09-04 15:28:32:28

elapsed time: 00:06:57

if the start and completion time is more that 24 hrs

elapsed time : 1.00:06:57

SELECT 
starttime,
completiontime,
CONCAT (
CASE
  WHEN GETDATBETWEEN(completiontime,starttime) > 0 THEN CONCAT(GETDAYBETWEEN(COMPLETIONTIME, STARTTIME),'.') 
  WHEN GETDATBETWEEN(completiontime,starttime) < 0 THEN ''
ELSE ''
END
CASE
  WHEN SUBTRACT(GETHOUR(completiontime), GETHOUR(starttime)) > 0 THEN CONCAT(SUBTRACT(GETHOUR(completiontime), GETHOUR(starttime)),':') 
  WHEN SUBTRACT(GETHOUR(completiontime), GETHOUR(starttime)) < 0 THEN CONCAT(SUBTRACT(GETHOUR(completiontime), GETHOUR(starttime)) + 24,':') 
ELSE '0:'
END
CASE
  WHEN SUBTRACT(GETMINUTE(completiontime), GETMINUTE(starttime)) > 0 THEN CONCAT(SUBTRACT(GETMINUTE(completiontime), GETMINUTE(starttime)),':') 
  WHEN SUBTRACT(GETMINUTE(completiontime), GETMINUTE(starttime)) < 0 THEN CONCAT(SUBTRACT(GETMINUTE(completiontime), GETMINUTE(starttime)) + 60,':') 
ELSE '0:'
END
CASE
  WHEN SUBTRACT(GETSECOND(completiontime), GETSECOND(starttime)) > 0 THEN CONCAT(SUBTRACT(GETSECOND(completiontime), GETSECOND(starttime)),':') 
  WHEN SUBTRACT(GETSECOND(completiontime), GETSECOND(starttime)) < 0 THEN CONCAT(SUBTRACT(GETSECOND(completiontime), GETSECOND(starttime)) + 60,':') 
ELSE '0'
END ) AS elapsed_time
FROM sample;

Thanks

Answer 1

You need to first get the time difference between the two timestamp values as a "raw" time value.
In Denodo, I believe you can convert the two values to milliseconds and get the difference as follows:

GETTIMEINMILLIS(Completiontime) - GETTIMEINMILLIS(Starttime)

You then need to substitute this "code" wherever you see @ms in the SQL statement below (i.e. "CASE WHEN @ms >=..." becomes "CASE WHEN GETTIMEINMILLIS(Completiontime) - GETTIMEINMILLIS(Starttime) >=..." etc.

SELECT CONCAT(
       CASE WHEN @ms >= 86400000 THEN CONCAT( @ms / 86400000, '.') ELSE '' END,                -- Days
       RIGHT('0' + CAST((@ms % 86400000) / 3600000 AS VARCHAR(2)), 2), ':',                    -- Hours        
       RIGHT('0' + CAST(((@ms % 86400000) % 3600000) / 60000 AS VARCHAR(2)), 2), ':',          -- Minutes
       RIGHT('0' + CAST((((@ms % 86400000) % 3600000) % 60000) / 1000 AS VARCHAR(2)), 2), '.', -- Seconds
       (((@ms % 86400000) % 3600000) % 60000) % 1000,                                          -- Milliseconds
        '') AS "Elapsed Time"   

If you don't need the milliseconds, just remove that line. The 86400000, 3600000, 60000 and 1000 values of course represent the number of milliseconds in a day, an hour, a minute and a second respectively. You need these to get the quotient and remainder for each component of the elapsed time that you need to display. I used your example data to test the outcome in SQL Server as displayed with the resulting elapsed time below, though I guess you'll need to use Denodo's GETTIMEINMILLIS in place of DATEDIFF_BIG. I know it sure ain't pretty. Would be much better to turn it into a User Defined Function!

Following your comment that Denodo has no RIGHT function (awkward!), I've adapted the above code to use REGEXP (which I understand Denodo DOES have!) as an alternative.

SELECT CONCAT(
       CASE WHEN @ms >= 86400000 THEN CONCAT( @ms / 86400000, '.') ELSE '' END,                         -- Days
       REGEXP(CAST((@ms % 86400000) / 3600000 AS VARCHAR(2)), '^(\d)$', '0$1'), ':',                    -- Hours        
       REGEXP(CAST(((@ms % 86400000) % 3600000) / 60000 AS VARCHAR(2)), '^(\d)$', '0$1'), ':',          -- Minutes
       REGEXP(CAST((((@ms % 86400000) % 3600000) % 60000) / 1000 AS VARCHAR(2)), '^(\d)$', '0$1'), '.', -- Seconds
       (((@ms % 86400000) % 3600000) % 60000) % 1000,                                                   -- Milliseconds
        '') AS "Elapsed Time"