Difference in between TIMEs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian Tkatch - 06 Dec 2005 20:41 GMT

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(TIMESTAMPDIFF(8, Diff)))
    || ':' || RIGHT(RTRIM('0' || CHAR(Mod(TIMESTAMPDIFF(4, Diff), 60))),
2)
    || ':' || RIGHT(RTRIM('0' || CHAR(Mod(TIMESTAMPDIFF(2, Diff), 60))),
2)
    )
    );

END
B.

Reply to this Message

Ian - 07 Dec 2005 03:22 GMT

> 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(Time_1 - Time_2),1,2) || ':' ||
substr(digits(Time_1 - Time_2),3,2) || ':' ||
substr(digits(Time_1 - Time_2),5,2)
else
raise_error('70001','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,

Reply to this Message

Brian Tkatch - 07 Dec 2005 17:31 GMT

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.

Reply to this Message

Brian Tkatch - 08 Dec 2005 15:34 GMT

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.

Reply to this Message

Knut Stolze - 07 Dec 2005 07:18 GMT

> CREATE FUNCTION TimeDiff(Time_1 TIME, Time_2 TIME)
> RETURNS TIME
[quoted text clipped - 20 lines]
> )
> );

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?

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Brian Tkatch - 07 Dec 2005 17:26 GMT

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.

Reply to this Message

Tonkuma - 07 Dec 2005 22:13 GMT

> substr(digits(Time_1 - Time_2),1,2) || ':' ||
> substr(digits(Time_1 - Time_2),3,2) || ':' ||
> substr(digits(Time_1 - Time_2),5,2)

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

Reply to this Message

Brian Tkatch - 08 Dec 2005 15:32 GMT

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.

Reply to this Message