Converting TDW Timestamps to DateTime in BIRT

We have previously published articles on how to convert TDW timestamps to "regular" timestamps in DB2. In BIRT, you may need to tackle the same problem again. The TDW timestamps can not be used for DateTime arithmetic and Charting functions in BIRT and has to be converted to regular DateTime. Though you can use the SQL to convert these timestamps to regular, in some cases, you may want to use the JavaScript integration of BIRT to convert them as well. Here is how to do it.

1. In your BIRT data set, ensure that you're selecting the TDW Timestamp that needs to be converted to the regular timestamp.
2. In the "Edit DataSet Window", click on Computed Columns and Click New button.
3. Give the column name for the new Computed Column, select the column data type as "Date Time".
4. In the expression field, click on the fX button and enter the following JavaScript code. Replace row["Timestamp"] with the appropriate Column name for the TDW Timestamp.

if (row["Timestamp"] == null)
{
null
}
else {
new Date(
(candleTime=row["Timestamp"],
(parseInt(1900) +
parseInt(candleTime.substr(0,3))) + "/" +
candleTime.substr(3,2)
+ "/" + candleTime.substr(5,2) + " " +
candleTime.substr(7,2) +
":" + candleTime.substr(9,2) + ":" +
candleTime.substr(11,2)
))
}

5. Goto the Preview Results and ensure that the newly Computed Column appears in the output.

This is the kind of method that ITM 6.2 built-in reports use and hope you find it useful.