Archive for June, 2011

Converting ARSystem Epoch time to Oracle TimeStamp

Friday, June 3rd, 2011

In ARSystem, all timestamp fields (CreateDate, LastModifiedDate, etc) are stored in Epoch format. If you don’t know, UNIX epoch is the elapsed # of seconds since January 1, 1970 12:00:00 AM GMT.

When viewing the data from the ARSystem, remedy automatically does the conversion process so that you always see the correct Date/Time.

TimeStamp from Epoch

 However, when doing a SQL query, all timestamp fields will return back a number in the format of “1307064623”. This makes it rather difficult when you wish to view the data in an application like SQL Developer, and have the data make sence.

The following SQL code will return back the Epoch values, but in a Date/Time format, as the example image to the right shows:

  to_char(cast(to_date(‘19700101′,’YYYYMMDD’)+createdate/86400 as timestamp with local time zone),’YYYY-MM-DD-YYYY HH:MI:SS AM’) as Epoch_TimeStamp
  datasetid = ‘BMC.ASSET’

 The date/time format can be changed depending on your needs. The example above uses ‘YYYY-MM-DD-YYYY HH:MI:SS AM’. By reformatting the string, your timestamp format will adjust accordingly.