Availability report from synthetic jobs Group by or aggregrate Daily

[CommunityUser]

Hi Team,

I'm a newbie to AppDynamics trying to generate a custom dashboard and Report. Right now, my synthetic Job runs for every 5mins and i would like to generate the Availability report from the job result success/failure aggregated per day. I was able to generate a report with ADQL 

SELECT toString(eventTimestamp, "MM-dd-yyyy") AS 'DATE', avg(metrics.`Availability (ppm)`)/10000 AS 'Availability %' FROM synth_session_records WHERE measurementSpec.scheduleName = 'JOB-NAME' 

But the date is shown in UTC timezone. The initial "eventTimestamp" is properly shown in PST but after doing the string conversion it is getting converted to UTC and the overall availability report shows incorrect percentage. 

I've tried to use the series functions to group the metrics but the resultant table has timestamp in the date column (i just need the MM-DD-YYYY date format and no any time in the tabular forum)

SELECT series(eventTimestamp, '1d') AS 'Date', avg(metrics.`Availability (ppm)`)/10000 AS 'Availability %' FROM synth_session_records WHERE measurementSpec.scheduleName = 'JOB-NAME' 

Output from the series query:

Date                                           Availability %

-------------------------------------------------------------

8/31/19 9:12:02 PM               85.58

9/1/19 9:12:02 PM                 90.12

9/2/19 9:12:02 PM                 84.92

Anythoughts on how i can aggregate the availability report per day with the proper timezone. 

[jeremyGoldstein · Accepted solution]

I'm also a novice.

Here's what I came up with. This subtracts 7 hours from the UTC time given by eventTimestamp.

Hope it helps!

tostring(eventTimestamp - toDate("7","H"), "MM/dd/yy h:mm a")

[CommunityUser]

This workaround helped me, although it is not the complete solution it can serve the purpose for now. When there is a daylight saving time, we need to change the difference in hours from 7 to 8.