I am calculating a TIMESTAMPDIFF from timestamps that can have a fairly large range of time intervals between them, from a few tenths of a second to 60 mins. Since the DB2 TIMESTAMPDIFF() function in DB2 returns an integer as a result, I am using microseconds as my numeric interval expression. TIMESTAMPDIFF DB2 documentation states:

Microseconds (the absolute value of the duration must be less than 3547.483648)

This equates to approximately ~59 minutes - so any interval over this amount returns as a null value which is the issue I'm trying to address.

Sample queries/timestamps I'm working with in the data:

select timestampdiff(1, char(timestamp('2022-09-12 14:30:40.444896') - timestamp('2022-09-12 14:30:40.115789'))) from sysibm.SYSDUMMY1

select timestampdiff(1, char(timestamp('2022-09-12 15:59:14.548636') - timestamp('2022-09-12 14:56:10.791140'))) from sysibm.SYSDUMMY1

The second query above is an example that returns a null value as the result exceeds the maximum result interval limit. I am pigeon-holed into using microseconds as my interval as results less than 1 whole second are still valid.

Are there any methods of working around this limit to return results exceeding the limit?

CodePudding user response：

When you subtract dates or timestamps, you end up with a duration..

A duration is a number formatted as yyyymmddhhmmss.zzzzzzzzzzzz.
From the manual:

A timestamp duration represents a number of years, months, days, hours, minutes, seconds, and
fractional seconds, expressed as a DECIMAL(14 s,s) number, where s is the number of digits of
fractional seconds ranging from 0 to 12. To be properly interpreted, the number must have the format
yyyymmddhhmmss.zzzzzzzzzzzz, where yyyy, mm, dd, hh, mm, ss, and zzzzzzzzzzzz represent,
respectively, the number of years, months, days, hours, minutes, seconds, and fractional seconds. The
result of subtracting one timestamp value from another is a timestamp duration with scale that
matches the maximum timestamp precision of the timestamp operands.

select timestamp('2022-09-12 15:59:14.548636') - timestamp('2022-09-12 14:56:10.791140') from sysibm.SYSDUMMY1;

returns 10303.757496

And is read as 1 hour, 3 minutes, 3.757496 seconds

So if you wanted to, you can do the math yourself. Better yet build your own UDF that returns a big integer or even larger as a decimal value.

CodePudding user response：

SELECT
  A, B
,   TIMESTAMPDIFF (2, CHAR (A - B)) * 1000
    (MICROSECOND (A) - MICROSECOND (B)) / 1000
  AS DIFF_MS
FROM 
(
  VALUES
  (timestamp('2022-09-12 14:30:40.444896'), timestamp('2022-09-12 14:30:40.115789'))
, (timestamp('2022-09-12 15:59:14.548636'), timestamp('2022-09-12 14:56:10.791140'))
) T (A, B)