Re: TIMESTAMPDIFF() result should be BIGINT instead of INTEGER -- MIDRANGE-L

Turns out my old IBM ID is still good, although the Contact email was out of date. I was able to muddle through and submit an RFE

http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=150467

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

On 5/12/2021 1:09 AM, Niels Liisberg wrote:

Peter it is a great idea - almost a design flaw of the timediff function.

If you are unfamiliar with the RFE system then I can post it for you - just
give me a shout . However I think you should give the RFE a try.

On Wed, May 12, 2021 at 9:09 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

You can create your own private IBM id.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Peter Dow
Sent: Mittwoch, 12. Mai 2021 08:39
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: TIMESTAMPDIFF() result should be BIGINT instead of INTEGER

Is it possible to do that without having an IBM customer ID?

On 5/11/2021 11:10 PM, Birgitta Hauser wrote:

If you think this would be a necessary enhancement, just open an RFE
(Request For Enhancements)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown) "If you think education is expensive, try ignorance."
(Derek Bok) "What is worse than training your staff and losing them? Not

training them and keeping them!"

„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Peter Dow
Sent: Dienstag, 11. Mai 2021 21:59
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: 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> /
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at

https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com