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

Document ID : KB000010993
Last Modified Date : 14/02/2018
Show Technical Document Details
Question:

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
Answer:

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.