Skip to main content

Hi,

Could you help me to find the average of timestamp like avg(timestamp)?

I have job execution end time like below and interested to find the average execution end time:

Sun Sep 6 2020 18:30:25

Sun Sep 6 2020 8:30:28

Sun Sep 6 2020 20:36:25

Sun Sep 6 2020 08:00:26

Sun Sep 6 2020 05:45:11

Sun Sep 6 2020 22:14:01

Thanks in advance!

Best Wishes,

Deepak

Hi Deepak,
I think the easiest way to calculate this will need some additional columns.
  1. First add a column with the start date of the execution
  2. Add a second column which calculates the difference between start and end timestamp in the preferred time unit using the DATEFIFF() function (see DATEDIFF support page)
  3. Calculate the average difference in time, using the AVG() function you already mentioned
  4. Add the average duration to the start timestamp, by using the Datetime modification functions. Use here the same time unit as used in step 2.

Hope this answer will help you.
Bests,
Jan-peter

As outlined by Jan you need to calculate an average on a timeframe between two acvitivites. DATEDIFF is one option to do so but there are several other options to do this based on your needs.

Check out this help page on it: https://confluence.celonis.com/display/PQLdevelopment/Throughput+Times

 

Best,

Kevin


Reply