unix time stamp handling via Jasper calculated fields

Products

• CA Cloud Service Management
• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

In the database the Date-Time field for custom attribute stores into the unix time stamps format. To convert the unix timestamp value to actual date-time format, please use the following instruction to create the calculated fields into the CSM advance report (jasper report).

Environment

Release:
Component: CACSM

Resolution

• Navigate to Reports >> Advance Reports 
• Go to the Create Adhoc View from Action Menu and select appropriate source. 
• Select the “Create Calculated Field” button which is available next to Label “Fields” into the Adhoc View. 

              

• Update the Field Name and put the below formula to convert the Date-Time type custom attribute and click validate button. 

                 EPOCH_TO_UTC_DATETIME (str2int("<custom attribute name>"))

• Once the validation shows successful, please save using the “Create Field” button, which can be used in ADHoc view.

Example : 

• There is Date-Time Custom attribute "estimated_end_date", which is in the unix date time format.  Using the calculated field you can display same attribute in date-time format as shown in below screenshots.