Convert UNIX epoch time data in the AutoSys DB via sql.

Products

• CA Workload Automation AE - Business Agents (AutoSys)
• CA Workload Automation AE - Scheduler (AutoSys)
• Workload Automation Agent

Issue/Introduction

Is there an easy way to convert the data in columns such as starttime, endtime, last_start, last_end or event_time_gmt in the AutoSys database tables within an sql query. They are stored in UNIX epoch time.

Environment

Product: Workload Automation AE 11.x Platform: Windows, UNIX, Linux Database: Oracle, Sybase, MS SQL

Resolution

Is there an easy way to convert the data in columns such as starttime, endtime, last_start, last_end or event_time_gmt in the AutoSys database tables within an sql query. They are stored in UNIX epoch time.

Solution:

Oracle Example:

   select job_name,   
   To_Char( To_Date( '01.01.1970 06:00:00','DD.MM.YYYY HH24:Mi:Ss')   
   + last_end / 86400,'MM/DD/YYYY HH24:Mi:ss') as LAST_END   
   FROM ujo_jobst   
   order by job_name;

Sybase/SQL server Example:

   select job_name,
   convert(char(30),(dateadd(ss,last_end - 21600,'1970-01-01 00:00:00')),109) as LAST_END
   from ujo_jobst
       order by job_name

In the above examples the 06:00:00 and 21600 represent the offset from GMT. Adjust accordingly.

• NOTE: AutoSys also offers a utility named "time0" which when run from a command line can convert the time as well.
  
  
• Consult the Unicenter AutoSys Job Management Reference Guide for more details.