Difference in between TIMEs

[Brian Tkatch]

I had two TIME fields that i wanted to know the difference in between
them. I saw TIMESTAMPDIFF but nothing equivalent for TIME. Further, i
want HH:MM:SS format. So, i wrote this FUNCTION.

Is there a better way to do it?

CREATE FUNCTION TimeDiff(Time_1 TIME, Time_2 TIME)
RETURNS TIME
SPECIFIC TimeDiff
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN ATOMIC

DECLARE Diff CHAR(22);

SET Diff = CHAR(TIMESTAMP( '1/1/0001', Time_1) - TIMESTAMP('1/1/0001',
Time_2));

RETURN TIME
(
CHAR
(
RTRIM(CHAR(TIME STAMPDIFF(8, Diff)))
|| ':' || RIGHT(RTRIM('0' || CHAR(Mod(TIMEST AMPDIFF(4, Diff), 60))),
2)
|| ':' || RIGHT(RTRIM('0' || CHAR(Mod(TIMEST AMPDIFF(2, Diff), 60))),
2)
)
);

END
B.

[Ian]

Brian Tkatch wrote:

I had two TIME fields that i wanted to know the difference in between
them. I saw TIMESTAMPDIFF but nothing equivalent for TIME. Further, i
want HH:MM:SS format. So, i wrote this FUNCTION.

Is there a better way to do it?

You can subtract two TIME columns? Result is a DECIMAL(6,0) = HHMMSS
(similar to TIMESTAMPDIFF return value).

$ db2 "values (current time - time('12:05:10' ))"

1
--------
73912.

1 record(s) selected.
73912 = 7H 39M 12S
So, you could write the function as:

CREATE FUNCTION TimeDiff(Time_1 TIME, Time_2 TIME)
RETURNS CHAR(8)
SPECIFIC TimeDiff
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
case when Time_1 >= Time_2 then
substr(digits(T ime_1 - Time_2),1,2) || ':' ||
substr(digits(T ime_1 - Time_2),3,2) || ':' ||
substr(digits(T ime_1 - Time_2),5,2)
else
raise_error('70 001','Time_1 must be greater than Time_2')
end;

Note CHAR(8) as return data type (Duration <> time). If you don't
agree, it's trivial to convert this to TIME.
Good luck,

[Knut Stolze]

Brian Tkatch wrote:

CREATE FUNCTION TimeDiff(Time_1 TIME, Time_2 TIME)
RETURNS TIME
SPECIFIC TimeDiff
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN ATOMIC

DECLARE Diff CHAR(22);

SET Diff = CHAR(TIMESTAMP( '1/1/0001', Time_1) - TIMESTAMP('1/1/0001',
Time_2));

RETURN TIME
(
CHAR
(
RTRIM(CHAR(TIME STAMPDIFF(8, Diff)))
|| ':' || RIGHT(RTRIM('0' || CHAR(Mod(TIMEST AMPDIFF(4, Diff), 60))),
2)
|| ':' || RIGHT(RTRIM('0' || CHAR(Mod(TIMEST AMPDIFF(2, Diff), 60))),
2)
)
);

Now I have a question for Serge: would it be better for the optimizer if no
compound statement were used an the "Diff" variable replaced in the calls
to TIMESTAMPDIFF with the expression above?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Brian Tkatch]

I was wondering *exactly* the same thing. :)

I went for the block because it's a little clearer, and in general, i
do not duplicating expressions because it is easier to maintain.

B.

[Brian Tkatch]

Oh, i didn't realize subtract resulted in a simple format, which is why
i used TIMESTAMPDIFF() to split it for me.

Your FUNCTION is mcuh easier to read, i think I'll switch. Thanx.

I do prefer TIME, if only because the two parameters are TIME. It just
kindof makes sense.

B.

[Tonkuma]

> substr(digits(T ime_1 - Time_2),1,2) || ':' ||

substr(digits(T ime_1 - Time_2),3,2) || ':' ||
substr(digits(T ime_1 - Time_2),5,2)

There are many ways to convert format.here is another example.
TRANSLATE('AB:C D:EF', CHAR(Time_1 - Time_2), 'ABCDEF')
CHAR can be replaced by DIGITS.

[Brian Tkatch]

That's a good one. I never thought of using TRANSLATE for formatting.

I'd prefer DIGITS here, if only because it is more specific than CHAR.

B.

[Brian Tkatch]

Apparently Raise_Error uses an external call.

db2 => CREATE FUNCTION A() RETURNS INT NO EXTERNAL ACTION RETURN
Raise_Error('', '')
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0374N The "EXTERNAL ACTION" clause has not been specified in the
CREATE
FUNCTION statement for LANGUAGE SQL function "<snip>.A" but an
examination of the
function body reveals that it should be specified. LINE NUMBER=1.
SQLSTATE=428C2

B.