Points and Periods of Time in the AE Database

The new year is more than a month old which means it’s about time for a blog article.

For that, I once more tackled a technical topic: today we look at statistics for periods of time.

While the statistics for periods of time are pretty straightforward (open selective statistics, enter criteria, done), it gets a little trickier for points in time. For that we need SQL.

Does the topic seem familiar to you? That’s quiet possible. Many, many years ago, I’d already written about this in my old blog.

As the topic is still up to date for many Automic Users, I decided to take it up again.

Statistics and Activities

Let’s get started with some useful general information.

As you know, all statistics are recorded in table AH.

As long as a task still is in the activity window, you will find current information about the task in table EH.

Particularly interesting is the status of the task, which is in EH_Status. In AH_Status, active tasks usually have the value 0, but you should not rely on that. There are some exceptions, for example, post-processing activities (this is the EH_Status 1574) will determine whether the return code is 0 or not (AH_Status ENDED_OK 1900) or not (AH_Status ENDED_NOT_OK 1800).

In the two databases, each job has several timestamps.

In table EH there are

• EH_ActivationTime: The activation time (= AH_TimeStamp1)
• EH_Starttime: The start time of the task (= AH_Timestamp2)
• EH_Endtime: The end time of the task, when everything is finished (even post processing)

In the table AH there’s even more information about time:

• AH_Timestamp1: The activation time (= EH_ActivationTime)
• AH_Timestamp2: The start time of the task (= EH_Startime)
• AH_Timestamp3: The end time of the task BEFORE post-processing. If there is no post-processing the value is NULL.
• AH_Timestamp4: The end time of the task AFTER post-processing (or the end of the task if there is no post-processing). It always has the same value as EH_Endtime.

Much Ado about Time Zones

As always with the Automation Engine, all timestamps are stored in UTC. In order to work with it, we must first transform them to our own time zone.

In my article about Design Patterns, I emphasized the importance of best practices. We can also apply one here:

Whenever possible, perform time zone transformation on the client side.

But sometimes, you have to or want to convert with SQL. For these cases, I’ve collected some useful SQL snippets that you can easily apply.

Current Timestamp (“now”) in UTC

--Oracle
systimestamp at time zone 'UTC'
--T-SQL
GETUTCDATE()

Conversion of Local Time to UTC
Replace [local timestamp to convert] with the timestamp you want to convert.

--Oracle
CAST(FROM_TZ(CAST([local timestamp to convert] AS TIMESTAMP),sessiontimezone) AT TIME ZONE 'UTC' AS DATE)
--T-SQL
Dateadd(minute, -DATEPART (TZoffset ,SYSDATETIMEOFFSET()), [local timestamp to convert])

Conversion of UTC timestamp to Local Time Zone
Replace [utc timestamp to convert] with the timestamp you want to convert.

--Oracle
CAST(FROM_TZ(CAST([utc timestamp to convert] AS TIMESTAMP), 'UTC') AT TIME ZONE sessiontimezone AS DATE)
--T-SQL
Dateadd(minute, +DATEPART (TZoffset ,SYSDATETIMEOFFSET()), [utc timestamp to convert])

What was Running at a Given Point in Time?

On July 27, 2018, at around 9:30 pm in Vienna, a total lunar eclipse happened.

Did you follow the exciting event as enthusiastically as me?

But do you also know what was happening on your Automation Engine at that point?

You can find out through SQL.

with UTCTIME as ( 
SELECT Dateadd(minute, -DATEPART (TZoffset ,SYSDATETIMEOFFSET()), '2018-07-27 21:30:00') as utctime --T-SQL 
--SELECT CAST(FROM_TZ(CAST(TO_DATE('2018-07-27 21:30:00', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),sessiontimezone) AT TIME ZONE 'UTC' AS DATE) as utctime from dual --Oracle 
 
) 
select AH_Client, AH_Otype, AH_IDNR, AH_Name, coalesce(EH_Status, AH_STatus) as StatusCode, AH_TimeStamp2, AH_TimeStamp4 
from AH left join EH on AH_IDNR = EH_AH_Idnr 
CROSS JOIN UTCTIME 
where exists (select 1 from UC_OTYP where AH_OType = OTYP_Type and OTYP_Executable = 1) -- only executable obj. types 
and AH_TimeStamp2 <= utctime and (AH_TimeStamp4 >= utctime or AH_TimeStamp4 is null);

Who was Logged in at a Point in Time?

User Sessions, too, create database entries.

This means that you only have to slightly change above query to find all user sessions active at a given point in time.

In below snippet, I made a tiny addition: I used a Join with the table USR to also get the users first name, last name and email. You may, of course, also access all other user information.

with UTCTIME as (
select Dateadd(minute,-DATEPART (TZoffset ,SYSDATETIMEOFFSET()), '2018-07-27 21:30:00') as utctime --T-SQL
--SELECT CAST(FROM_TZ(CAST(TO_DATE('2018-07-27 21:30:00', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),sessiontimezone) AT TIME ZONE 'UTC' AS DATE) as utctime from dual --Oracle
)
select AH_Client, AH_IDNR, AH_Name, AH_Status, AH_TimeStamp1, AH_TimeStamp4, USR_FirstName, USR_LastName, USR_EMail1
from AH
CROSS JOIN UTCTIME
inner join USR on AH_OH_Idnr = USR_OH_Idnr
where AH_OType = 'USER'
and AH_TimeStamp1 <= utctime and (AH_TimeStamp4 >= utctime or AH_TimeStamp4 is null);

What was Running Parallel to a Specific Task?

Let’s rephrase this requirement.

We are looking for all tasks, that fulfill at least one of the following conditions:

• The task started after the reference task started and before it ended.
• The task ended after the reference task started and before it ended.

The following diagram shows some examples.

The red arrow is the reference task. The green arrows are tasks, the script should filter. The violet arrows are irrelevant tasks, that don’t run parallel to the reference task.

The query for this is a simple modification of the first example in this article.

Only difference: In the CTE, we do not determine the UTC time of a fixed time, but the relevant time stamp of the reference task. To identify the red task, the query uses the RunID, here 08154711.

with TASKTIMES as ( 
       select AH_Timestamp2 as TASKSTART, AH_Timestamp4 as TASKEND from AH 
       where AH_Idnr = 08154711 
) 
select AH_Client, AH_Otype, AH_IDNR, AH_Name, coalesce(EH_Status, AH_STatus) as StatusCode, AH_TimeStamp2, AH_TimeStamp4 
from AH left join EH on AH_IDNR = EH_AH_IDNR 
CROSS JOIN TASKTIMES 
where exists (select 1 from UC_OTYP where AH_OType = OTYP_Type and OTYP_Executable = 1) -- only executable obj. types 
and AH_TimeStamp2 <= TASKEND and (AH_TimeStamp4 >= TASKSTART or AH_TimeStamp4 is null) 
order by AH_Client, AH_TimeStamp2, AH_TimeStamp4; 

Do You Want More SQL?

Lately, technical articles with a fous on SQL and the AE database were rare in my blog. Do you want more of them? It would be great if you could tell me in the comments.

And if you want to learn a lot more about the topic: There are still some free spots for the AE Database Knowledge Workshop (GERMAN version) in Nuremberg from 25. to 27. of March.