Null Results From TIMESTAMPDIFF in DB2 SQL

Question

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?

Mark Barinstein · Answer 1 · 2022-09-19T12:01:13.573

SELECT
  A, B
, (
    (DAYS (A) - DAYS (B)) * DEC (86400, 31)
  + MIDNIGHT_SECONDS (A) - MIDNIGHT_SECONDS (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)

A	B	DIFF_MS
2022-09-12 14:30:40.444896	2022-09-12 14:30:40.115789	329
2022-09-12 15:59:14.548636	2022-09-12 14:56:10.791140	3783758

Update
Just in case. You should be familiar with TIMESTAMPDIFF specific.
It works on timestamp DURATIONs (in the yyyymmddhhmmss.zzzzzzzzzzzz format, but I've truncated the z's part) and may produce quite a "surprising" result, if the difference is more than 1 month.

SELECT 
  DATE (A) AS A, DATE (B) AS B
, DAYS (B) - DAYS (A) AS DIFF_REAL
, TIMESTAMPDIFF (16, CHAR (B - A)) AS DIFF_TS
, INT (CHAR (B - A)) AS DURATION
, (DAYS (B) - DAYS (A)) * 86400 AS DIFF_REAL_SEC
, TIMESTAMPDIFF (2, CHAR (B - A)) AS DIFF_TS_SEC
FROM 
(
  VALUES 
  ('2022-01-01'::TIMESTAMP, '2022-02-01'::TIMESTAMP)
, ('2022-01-31'::TIMESTAMP, '2022-03-01'::TIMESTAMP)
, ('2022-02-01'::TIMESTAMP, '2022-03-01'::TIMESTAMP)
, ('2022-02-01'::TIMESTAMP, '2022-02-28'::TIMESTAMP)
) T (A, B)

A	B	DIFF_REAL	DIFF_TS	DURATION	DIFF_REAL_SEC	DIFF_TS_SEC
2022-01-01	2022-02-01	31	30	1,00,00,00,00	2 678 400	2 592 000
2022-01-31	2022-03-01	29	31	1,01,00,00,00	2 505 600	2 678 400
2022-02-01	2022-03-01	28	30	1,00,00,00,00	2 419 200	2 592 000
2022-02-01	2022-02-28	27	27	27,00,00,00	2 332 800	2 332 800

So, in short: don't use DURATIONs and TIMESTAMPDIFF, if you want to calculate the difference between 2 timestamps in days, hours, minutes, etc., if the difference is more than 1 month.

score 0 · Accepted Answer · answered Sep 16 '22 at 20:47

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.

"Keep it simple stupid" to my self right now. Tests seem to pass on this simple solution, use of timestampdiff() does not seem to be needed at all for this use-case. Thank you for the reply. — vann447, Sep 19 '22 at 12:23

Null Results From TIMESTAMPDIFF in DB2 SQL

2 Answers2