RE: Need to find job start, job end, calculate total job time for a group of users, TST*, for a selected date range

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

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Friday, April 21, 2017 12:28 PM
To: Midrange Systems Technical Discussion
Subject: Re: Need to find job start, job end, calculate total job time for a group of users, TST*, for a selected date range

select x.message_timestamp as start_time,
y.message_timestamp as end_time,
y.message_timestamp - x.message_timestamp as job_duration,
x.from_job, y.from_job
from table(qsys2.history_log_info(current date - 2 days, timestamp((current date - 1 days)) - 1 seconds)) x full outer join table(qsys2.history_log_info(current date - 2 days, timestamp((current date - 1 days)) - 1 seconds)) y using (from_job) where x.message_id = 'CPF1124' and y.message_id = 'CPF1164';

There are rare occasions where the end time is before the start type, by milliseconds.

I'll leave it up to you to convert this decimal
y.message_timestamp - x.message_timestamp as job_duration, to a time or timestamp field.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Rob Berendt <rob@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 04/21/2017 11:57 AM
Subject: Re: Need to find job start, job end, calculate total
job time for a group of users, TST*, for a selected date range
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Yes. Upgrade to a more recent level of the OS and then look at the
"Message Handling Services" at
http://ibm.biz/DB2foriServices

Rob Berendt