RE: Need to find job start, job end, calculate total job time

Sue,

My result, (time1 - time2) is actually hhmmss.
The result is formatted as a 6.0 dec
83,107
Are there any format options?
Can the 83,107 appear as 8:31:07?

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Sue Romano
Sent: Saturday, April 22, 2017 1:16 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Need to find job start, job end, calculate total job time

What you have when you subtract dates, times, or timestamps is called a duration. Many of the datetime functions work on durations. Perhaps this will help you get to the answer you are looking for.

select hour(timedur), minute(timedur), second(timedur) from
(values current time - (current time - 2 hours - 5 minutes - 33
seconds) ) x (timedur);

HOUR ( TIMEDUR ) MINUTE ( TIMEDUR ) SECOND ( TIMEDUR )
2 5 33

Sue Romano
IBM i SQL Development

For start of job -
DSPLOG OUTPUT(*PRINT) MSGID(CPF1124)
CRTPF FLASTR
CPYSPLF QPDSPLOG FLASTR

For end of job -
DSPLOG OUTPUT(*PRINT) MSGID(CPF1164)
CRTPF FLAEND
CPYSPLF QPDSPLOG FLAEND

SELECT substr(FLASTR,31,58) , substr(FLAend,58,30) ,
time(substr(flaend,79,8)) -
time(substr(flastr,81,8))
FROM qgpl.flastr
JOIN qgpl.flaend on substr(FLASTR,31,26) = substr(flaend,31,26) where
substr(FLASTR,38,3) = 'FLA'
and substr(FLASTR,47,4) = 'QPAD'

SUBSTR SUBSTR Numeric

Expression

793387/FLAGBRIG/QPADEV004F started on 04/01/17 at 08:59:00 ended on

04/01/17 at 17:30:07; 83,107

793377/FLAAKHAL/QPADEV0040 started on 04/01/17 at 08:56:39 ended on

04/01/17 at 17:30:14; 83,335

793376/FLACWILS/QPADEV003Z started on 04/01/17 at 08:56:27 ended on

04/01/17 at 17:30:14; 83,347

794215/FLASBJOR/QPADEV005J started on 04/01/17 at 10:28:44 ended on

04/01/17 at 17:30:57; 70,213

794268/FLADWILL/QPADEV005S started on 04/01/17 at 10:33:22 ended on

04/01/17 at 18:58:58; 82,536

Can the difference be shown in hours, minutes, seconds?
I tried using timestampdiff, not a valid command.

Paul

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

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD