**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 wont 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!

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;
```

An easier method would be to use the data type Interval.

To get the seconds between two datetimes:

```
interval_to_char( $dtFrom - $dtTo , 'SS');
```

Takes an interval and returns a character representation of the interval.

interval_to_char( [in] InputInterval As interval, [in] FormatString As varchar ) As varchar

This function could also be used to get minutes and hours.

Similar Questions

Ok, I have two related lists on my linux box in text files: /tmp/oldList /tmp/newList I need to compare these lists to see what lines got added and what lines got removed. I then need to loop over t

I am using MSSQL 2008 Standard I have multiple rows in a select command which are filled with events. For every event I have got a timestamp, now I want to calculate the time between the events: (numb

This question already has an answer here: Get difference between 2 dates in javascript? 7 answers I got two Date objects and I want to calculate the difference in hours. If the difference in ho

In grails we can define an action using 2 ways:- def actionname() { } and def actionname = { } What is the difference between the two styles ? When i tried to insert an spring security annotation ab

I am very new to php, if anyone can help me by telling what the difference is between these two cron commands, I would be very thankful: /usr/local/bin/php -q /home/delightl/public_html/myfolder/run.

I have an application that requires the elapsed minutes between two times. Eg: 9:00 to 10:00 is sixty minutes. This is the solution that I worked out, but it seems excessively verbose. Can I do better

Given two strings of length n,P = p1...pn and Q = q1...qn, we define M(i, j, k) as the number of mismatches between pi...pi+k-1 and qj..qj+k-1. That is in set notation, M(i, j, k) refers to the size o

I have a train schedule app, and I am trying to calculate the difference between two times with the following format HH:MM, so my users can easily see if the train is late (note: I am fetching the re

I want to get the difference between two DateTime objects as a DateTime and not as a Period or something like that. How can I do that ?

I have these two datatables and I want to get the difference between them. Here is an example: Table1 ------------------------- ID | Name -------------------------- 1 | A 2 | B 3 | C -----------------

What is the difference regarding memory allocation and efficiency between using a struct with two fields and a pair?

what is difference in this two condition return Convert.ToInt32(cmd.ExecuteScalar()) == 1 ? true : false; return res =! 0 ? true:false; return Convert.ToInt32(cmd.ExecuteScalar()) > 0 ? true : fals

Given two arrays, A and B, i want to find a number from A and a number from B, such that the absolute difference between the two numbers is the smallest. Eg: A = 1 2 9 B= 4 5 6 Ans : 2,4 as Math.abs(2

This question already has an answer here: Getting time span between two times in C#? 4 answers I have two textboxes that allow a user to enter a start time and an end time in this format (h:mm)

I have a datagridview in my application which holds start and finish times. I want to calculate the number of minutes between these two times. So far I have got: var varFinish = tsTable.Rows[intCellRo

I want to ask about the difference between these two queries: select ProductName = case when p.ProductID is null then 'Unknown' else p.ProductName end and select p.ProductName = case when p.ProductID

How do I calculate the time difference in milliseconds between two timestamps in Oracle?

LONG STORY SHORT I have a file called timetable.pl that has a pradicate (r,s,t)- r is the name of the train, s is the list of stations it stops at and t is a list of given times for the them stops. TH

Is there a more efficient way of doing this below? I want to have the difference in years between two dates as a single scalar. Any suggestions are welcome. from datetime import datetime start_date =

This question already has an answer here: Calculate difference between two dates (number of days)? 7 answers I've been trying many ways to calculate the round number of days between two dates,

I have two masked TextBox controls and was wondering how I'd go about getting the time in each one and then converting the difference into milliseconds. Like, say in tb1 I write 12:01 and in tb2 I w

I have two vectors: a<-1:100 b<-sample(1:100,80) I would like to display those elements of a that are not included in b. I have tried subset(a,a!==b) and a[a!==b] but these didn't work. What am

I have two lists of dictionaries and I'd like to find the difference between them (i.e. what exists in the first list but not the second, and what exists in the second list but not the first list). T

What is the difference between these two function pointer notations in C? void (*a[]()) and void (*a)()[] Do they both represent same - a as an array of pointers to functions - or does the second one

What is the difference between these two ? // Get response using (HttpWebResponse response = request.GetResponse() as HttpWebResponse) { // Get the response stream StreamReader reader = new StreamRead

In my book they've been switching the way the declare arrays between the following two methods: int array1[] = {1, 2, 3}; int[] array2 = {1, 2, 3}; I was wondering what the difference was between th

This question already has an answer here: Get difference between 2 dates in javascript? 7 answers How to calculate the difference between two dates say date 1 is 28-04-2014 and date 2 is 30-04-

This question already has an answer here: T-SQL - Left Outer Joins - Filters in the where clause versus the on clause 6 answers Is there a logical difference between putting a condition in the O

I am not able to understand that what is the difference between these two operations :- abc == null null == abc Both of these expressions result in same output. I have searched many places but was n

In my custom module I am unable to calculate difference between two dates. My field is 'date_s':fields.datetime('Start Date'), 'date_e':fields.datetime('End Date'), I want to calculate the differenc

I'm a beginner in C. Is there any datatype for dates? In C we have for working with time, is there one for dates too? How can I calculate difference between two dates?

var gem = function() { }; function gem() { }; It looks to me they are the same. but I know there must be some difference between those two? Anyone knows which one is better?

This is the situation here. I have current time in epoch. also I have number of days. I want to find the difference between the 2 to give some past time in epoch format. i.e. currentEpochTime - (x d

In python I am computing the difference between two images uses ImageChops.difference is there a faster way to do this computation? Since it's relatively slow on 720p images, I let it run for about 6

This question already has an answer here: How do I get the difference between two Dates in JavaScript? 11 answers I'm working on an assignment where I must use Javascript. In my application a u

This question already has an answer here: Calculate difference between 2 date / times in Oracle SQL 9 answers How to find difference between two dates in oracle? I tried with Extract method but

i Get some Problem with SQL , the problem is find the Difference between two record not in the same row . i take picture of the Excel sheet to illustrate what i mean . i need to find the difference be

I have been using a tidy little routine that I found here to calculate the difference in days between two dates in AS3. I am getting some strange results and I am wondering if any of you inter-codal-m

What is the difference between the following two representations of a field password in a jsp? Sorry if it is a novice question but I am pretty much that... ! <td class=inputtext> <html:pas

Whats the difference between these two comparison statments? var result = EqualityComparer<T>.Default.Equals(@this, null); var result = @this == null; Obviously the aim is to test whether the o

How do I calculate the difference between the top one and the bottom one with jquery? <span class=field-content>9.75</span> <span class=field-content>4.75</span>

What is the difference between these two clips of code? The first clip is labeled This one: and the second clip is labeled And this:. (.rb) class Reseller < ActiveRecord::Base attr_accessible :

How can I find difference between two time intervals. Like 13:45:26.836 - 14:24:18.473 which is of the format Hour:Min:Sec:Millisecs. Now i need to find the time difference between these two times.

I've misplaced += with =+ one to many times, and I think I keep forgetting because I don't know the difference between these two, only that one gives me the value I expect it to, and the other does no

On Matlab I have two BIN image. Can I computing difference of gray scale pixel by pixel and show it on the histogram? (The images have same size)

working on a script, that calculates the difference between dates retrieved from an API. I want it to then drop in an image as many times as the difference. $d1 = new DateTime('2012-04-04'); $d2 = ne

I have two date columns in my date frame. I can find difference between this dates using: issues <- transform(issues, duration = difftime(strptime(close_date, format=%d.%m.%Y), strptime(created_o

I was working on fragments and came across two things Activity and FragmentActivity which are used several times. I want to know that is there any difference between these two, because when I changed

So, I am wondering whats the difference between these two segments of code - other than just syntax differences? BBDataStore* tempDataStore = [[BBDataStore alloc] initWithDataExpiry:DATA_EXPIRY_TIME];

I have two times in an array and I need to calculate the difference between both of these. I have converted the hours into mins, then added the remaining mins. This gives me the total minuites overall