Hi all,

I am trying to determine the difference between two timestamps in
milliseconds. Is there any easy or difficult method of getting this done?

DB2 7.1 UDB on Windows XP

Regards

Rudolf Bargholz

See the online ref.
You will have to do some calculations on the timestamp difference to get
seconds etc from the years, months, days

-------------------------------------------------------------------------------------------
Subtracting Timestamps
The result of subtracting one timestamp (TS2) from another (TS1) is a
timestamp duration that specifies the number of years, months, days,
hours, minutes, seconds, and microseconds between the two timestamps.
The data type of the result is DECIMAL(20,6).

If TS1 is greater than or equal to TS2, TS2 is subtracted from TS1. If
TS1 is less than TS2, however, TS1 is subtracted from TS2 and the sign
of the result is made negative. The following procedural description
clarifies the steps involved in the operation result = TS1 - TS2:

If MICROSECOND(TS2) <= MICROSECOND(TS1)
then MICROSECOND(RESULT) = MICROSECOND(TS1) -
MICROSECOND(TS2).

If MICROSECOND(TS2) > MICROSECOND(TS1)
then MICROSECOND(RESULT) = 1000000 +
MICROSECOND(TS1) - MICROSECOND(TS2)
and SECOND(TS2) is incremented by 1.

The seconds and minutes part of the timestamps are subtracted as
specified in the rules for subtracting times.

If HOUR(TS2) <= HOUR(TS1)
then HOUR(RESULT) = HOUR(TS1) - HOUR(TS2).

If HOUR(TS2) > HOUR(TS1)
then HOUR(RESULT) = 24 + HOUR(TS1) - HOUR(TS2)
and DAY(TS2) is incremented by 1.

The date part of the timestamps is subtracted as specified in the rules
for subtracting dates.
-------------------------------------------------------------------------------------------

You will have to do some calculations on the timestamp difference to get
seconds etc from the years, months, days

Rudolf Bargholz wrote:

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

This will do micro seconds. Divide by 1000 to get milliseconds...


CREATE FUNCTION F.MICROSECONDS (X TIMESTAMP, Y TIMESTAMP)
RETURNS BIGINT
SPECIFIC F.MICROSECONDS
LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN
(DAYS(X) - DAYS(Y)) * BIGINT(86400000000) +
MIDNIGHT_SECONDS(X) * BIGINT(1000000)
- MIDNIGHT_SECONDS(Y) * BIGINT(1000000)
+ MICROSECOND(X)
- MICROSECOND(Y)
;

Regards
Paul Vernon
Business Intelligence, IBM Global Services