Convert Candletime to Timestamp for Cognos report

Body

Date/time stored in Tivoli Data Warehouse by IBM Tivoli Monitoring agent is in Candletime format and not in Timestamp format.

Candletime Format = CYYMMddhhmmssSSS
where
C – Century, YY – Year, MM – Month, dd – Day, hh – Hour, mm – Minute, ss – Second, SSS - Milliseconds
CYY - (to be added to 1900 as candle time assumes 1900 as the base)

Example:
Candletime= 1130501181545000

Century with year = CYY = 113
Year = 1900+113 = 2013
Month=05
Day=01
Hour=18
Mins=15
Seconds=45
Milliseconds=000

For Cognos, the time stamp should be in the following format:
2013-05-01 18:15:45.000


WRITETIME is the column in the Warehouse which contains the date/time in Candletime format. And in the Cognos model or report, WRITETIME is referred in a data item. Lets say, it is [Consolidation View].[CPU Usage - NT].[WRITETIME]. So, now lets add a new query/data item in that query subject and add the following

cast((cast((cast(substring([Consolidation View].[CPU Usage - NT].[WRITETIME], 1, 3), integer) + 1900) as char(4)) + '-' +
substring([Consolidation View].[CPU Usage - NT].[WRITETIME], 4,2) + '-' +
substring([Consolidation View].[CPU Usage - NT].[WRITETIME], 6,2) + ' ' +
substring([Consolidation View].[CPU Usage - NT].[WRITETIME], 8,2) + ':' +
substring([Consolidation View].[CPU Usage - NT].[WRITETIME], 10,2)+ ':' +
substring([Consolidation View].[CPU Usage - NT].[WRITETIME], 12,2) + '.000')
as timestamp)

This data item will have the date/time in Timestamp format which the report can understand.

Reference article: Reporting with Tivoli Data Warehouse