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