Difference between two times in HH:MI:SS

Question

My Question: I need to get the difference between two dates (times) in the format HH:MI:SS using a script. For example if I had two times:

2012/08/24 13:04:23
2012/08/24 14:15:32

My result would be:

1:11:09

Requirements: I'm writing this using SAP's BusinessObjects Data Services Designer, so my script must satisfy their syntax rules (click here to see long PDF document... If you're not familiar with SAP's syntax, just know that it's really limited...).

---

What I've tried: Currently I have a function (see below) which will give the user a SQL statement they can use in the SQL function which will give them what they're looking for, but I would like to make this data store agnostic.

Here's my current function (not really what I'm looking for):

#Use this with a SQL function. For example: SQL('DSS_USER', GetTimeDifferenceSQL($time_1, $time_2));

return '
with seconds as (
  select 24*60*60*(to_date(\'[$time_1]\', \'YYYY.MM.DD hh24:mi:ss\')
                    - to_date(\'[$time_2]\', \'YYYY.MM.DD hh24:mi:ss\')) seconds_diff 
  from dual
)
select to_char(trunc(sum(seconds_diff)/3600), \'FM999999990\')  || \':\' || to_char(trunc(mod(sum(seconds_diff),3600)/60), \'FM00\') || \':\' || to_char(mod(sum(seconds_diff),60), \'FM00\')
from seconds
';

That doesn't work because I want this to stand alone from a data store and be calculated in the script alone.

I've also tried converting the dates into decimal numbers and then using the JED_Time(int) function on them, and that works, except decimal is base 10 and time is base whatever... So that won't work.

---

My Hold-up: I'm having a hard time with the fact that time doesn't really have a defined base.... Any help would be really great! Thanks!

Answer 1

This may or may not be totally accurate, but I think I got it. Input is more than welcome. Hopefully this will help someone one day!

#Make sure that when we're finding the difference, we always take the lesser date from the greater date. We'll negate it at the end.
if ($time_1 < $time_2)
begin
  $temp_time = $time_1;
  $time_1 = $time_2;
  $time_2 = $temp_time;
end

#Get all value differences from the two times
$nanoseconds = to_decimal(to_char($time_1, 'FF'), '.', null, 0) - to_decimal(to_char($time_2, 'FF'), '.', null, 0);
$seconds = to_decimal(to_char($time_1, 'SS'), '.', null, 0) - to_decimal(to_char($time_2, 'SS'), '.', null, 0);
$minutes = to_decimal(to_char($time_1, 'MI'), '.', null, 0) - to_decimal(to_char($time_2, 'MI'), '.', null, 0);
$hours = to_decimal(to_char($time_1, 'HH24'), '.', null, 0) - to_decimal(to_char($time_2, 'HH24'), '.', null, 0);
$days = interval_to_char($time_1 - $time_2, 'D');

#fix nanoseconds
if ($nanoseconds >= 1000000000)
begin
  $seconds = $seconds + 1;
  $nanoseconds = $nanoseconds - 1000000000;
end

if ($nanoseconds < 0)
begin
  $seconds = $seconds -1;
  $nanoseconds = $nanoseconds + 1000000000;
end

#fix seconds
if ($seconds >= 60)
begin
  $minutes = $minutes + 1;
  $seconds = $seconds - 60;
end

if ($seconds < 0)
begin
  $minutes = $minutes -1;
  $seconds = $seconds + 60;
end

#fix minutes
if ($minutes >= 60)
begin
  $hours = $hours + 1;
  $minutes = $minutes - 60;
end

if ($minutes < 0)
begin
  $hours = $hours -1;
  $minutes = $minutes + 60;
end

#fix hours
if ($hours >= 24)
begin
  $days = $days + 1;
  $hours = $hours - 24;
end

if ($hours < 0)
begin
  $days = $days - 1;
  $hours = $hours + 24;
end

#fix days
if (trunc($days/365, 0) >= 1)
begin
  $years = trunc($days/365, 0);
  $days = $days - ($years * 365);
end

if (round($days/7, 0) > 0)
begin
  $weeks = round($days/7, 0);
  $days = $days - ($weeks * 7);
end

$ret = '';

if ($years > 0)
begin
  $ret = $years||' year'||ifthenelse($years = 1, '', 's')||' ';
end

if ($weeks > 0)
begin
  $ret = $ret||$weeks||' week'||ifthenelse($weeks = 1, '', 's')||' ';
end

if ($days > 0)
begin
  $ret = $ret||$days||' day'||ifthenelse($days = 1, '', 's')||' ';
end

$ret = $ret||$hours||':'||lpad($minutes, 2, '0')||':'||lpad($seconds, 2, '0')||':'||lpad(round($nanoseconds/1000000, 0), 3, '0');

#Negate it if the parameter values were swapped at the beginning (using $temp_time)
if ($temp_time is not null)
begin
  $ret = '-'||$ret;
end

return $ret;