TIMESTAMPDIFF() result should be BIGINT instead of INTEGER

Hi Everyone,

On a v7r3 box, I was using timestampdiff to determine the duration of a job in microseconds.  I'd never used it before, but it seemed pretty straightforward, but the result was  ++++++++++++++

SELECT JOB_NAME
     , JOB_ACTIVE_TIME
     , JOB_END_TIME
     , TIMESTAMPDIFF(1, char(JOB_END_TIME - JOB_ACTIVE_TIME)) uSeconds
   FROM TABLE(QSYS2/JOB_INFO( JOB_USER_FILTER => 'myusrid')) JI
  WHERE JOB_NAME = '123456/myusrid/myjob'

 JOB_NAME                    JOB_ACTIVE_TIME JOB_END_TIME               USECONDS
 123456/myusrid/myjob    2021-05-08-06.20.48 2021-05-08-07.46.29         ++++++++++++++


The problem is that for some reason, TIMESTAMPDIFF() returns INTEGER.  INTEGER is of course 4 bytes or 2^31 = 2,147,483,648 (signed).  That many microseconds is about 35 minutes and 47 seconds. Any job whose duration is longer gets SQLSTATE 22001 or diagnostic message CPD5036 "Data mapping error on member QSQPTABL." reason 27, which is "27 -- For the CAST operation, the character destination field is too short to hold all the significant digits of the source."

And when there is no explicit CAST operation in the SQL statement, and several possible implicit CAST operations, time is wasted figuring out where the problem actually is.

It seems like BIGINT would have been a better choice. 8 bytes gives 2^63 or 9,223,372,036,854,775,808 microseconds (signed), or over 106,751,991 days. The consumer of the result can choose to put their result into a smaller variable.

These days, microseconds is overkill, so I dropped back to just seconds to resolve my problem, but it still seems like a poor choice of result size for TIMESTAMPDIFF.


--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /