Question

User defined DB2 SQL functions not converting the date time to User time zone while extracting report from Report definition

We are trying to extract an excel report of cases by utilizing Report Definition and we have written an activity by passing Report deifintion to "pxRetrieveReportData" (OOTB Pega activity).

We have utilised SQL Functions to format the "pxCreateDateTime" for the excel report in Report definition. However when Report defintion extracts the results, it does not convert the formatted "pxCreateDateTime" to User time zone and instead provides results in MST time zone ( as the date time is stored in Pega Database in MST time zone).

The Pega database that were are using is IBM DB2 and following SQL function were tried to extract the pxCreateDateTime in User time zone. 

1. VARCHAR_FORMAT(CAST({1} AT LOCAL AS TIMESTAMP), 'MM/DD/YYYY HH:MI AM' )
Here {1} is pxCreateDateTime

2. VARCHAR_FORMAT(CAST( {1} AT TIME ZONE (CASE WHEN {2}='US/Eastern'
   THEN '-05:00' 
   ELSE
   '-07:00'
   END) AS TIMESTAMP), 'MM/DD/YYYY HH:MI AM' )
Here {1} is pxCreateDateTime and {2} is User time zone

However when the above SQL functions are used in DB2 console, they are providing expected results. But when these SQL functions are invoked from Pega; the results are not getting converted to User time zone.

Kindly let me know if any other alternative SQL functions in DB2 are available for time zone conversion along with formatting of date time. 
Also let me know if any one faced the similar issue with the usage of Report definition to extract the cases to excel report by utilizing SQL functions

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Correct Answer
December 12, 2016 - 3:47am

As part of solution, following steps were followed

Step 1: Created User defined java function by passing Operator time zone in Pega Operator profile to get the offset.

Step 2: Pass the offset as a parameter to the Report Definition to get the conversions of the Date time in Operator Time zone

Step 3: Create SQL function to be used in Report Definition as below 

 VARCHAR_FORMAT( (CAST{1} AT TIMEZONE {2}) , "MM/DD/YYYY HH:MI")

where {1} is the Date time column and {2} is offset parameter (calculated in Step 1) passed to Report Definition

Step 4: Pass the all the retrieved results from Report definition to "MSOGenerateExcelFile" OOTB activity to generate the Excel.

Comments

Keep up to date on this post and subscribe to comments

Pega
November 30, 2016 - 2:12am

However when the above SQL functions are used in DB2 console, they are providing expected results. But when these SQL functions are invoked from Pega; the results are not getting converted to User time zone.

For the SQL functions invoked from Pega, you mentioned that they are not getting converted over. How and where are you validating this (Clipboard, exported excel file, report viewer)?

Also, share the example value seen in Pega and in the DB2 console.

December 2, 2016 - 11:33am
Response to nistr

We are validating in Excel report generated through Export to Excel option. But the Export to Excel is customised and we are not using what Pega provided in Report definition displays.

For DB2 console, I currently do not have screen shots. Will share once got. However have uploaded the excel sheet.

In this spreadsheet, the Creation date is exported as "10/27/2016 09:57 PM" MST time zone at which DB2 is set. But the user time zone where he is exporting the excel report is GMT. So the expected result in the excel report was "10/27/2016 04:57 PM".

 

 

 

 

December 2, 2016 - 11:35am
Response to SeemaB65

Also, today when we dynamically sent the user time zone to these SQL functions and invoked from Pega; the solution worked. However we have to thoroughly test in all User time zones.

Pega
December 5, 2016 - 8:06am
Response to SeemaB65

Great. It would be great to understand what changed to make this work.

Pega
December 5, 2016 - 8:06am
Response to SeemaB65

But the user time zone where he is exporting the excel report is GMT. So the expected result in the excel report was "10/27/2016 04:57 PM".

Kindly clarify if the user's machine, when the browser is running is in GMT or is the operator timezone as set in operator profile in the Pega platform is in GMT or the application server (Tomcat or JBoss or Websphere or Weblogic) is in GMT.

December 6, 2016 - 9:18am
Response to nistr

Its the operator timezone set in operator profile of Pega is in GMT and the application server timezone is in MST. Would post the detailed solution tomorrow.

December 12, 2016 - 3:47am
Response to SeemaB65

As part of solution, following steps were followed

Step 1: Created User defined java function by passing Operator time zone in Pega Operator profile to get the offset.

Step 2: Pass the offset as a parameter to the Report Definition to get the conversions of the Date time in Operator Time zone

Step 3: Create SQL function to be used in Report Definition as below 

 VARCHAR_FORMAT( (CAST{1} AT TIMEZONE {2}) , "MM/DD/YYYY HH:MI")

where {1} is the Date time column and {2} is offset parameter (calculated in Step 1) passed to Report Definition

Step 4: Pass the all the retrieved results from Report definition to "MSOGenerateExcelFile" OOTB activity to generate the Excel.

December 12, 2016 - 3:39am

After extensive testing, we discovered that as we are calculating and passing the offset based on Operator time zone and not the specified Date time; though we are able to get the conversions of the Date time in Operator Time zone, it does not provide accurate conversions by taking Daylight Savings into consideration. 

For example: If the case was created in Austria in April on 04/12/2016 4:15 PM( i.e; when Austria was following DST) and that case is retrieved today (i.e.; when Austria is not following DST) by passing the offset based on Operator Time zone, we would get result as 04/12/2016 3:15 PM. This is because the offset is getting calculated based on current date time. So the conversion is based on offset less DST.

So if there is any option that we can utilize java based user defined functions in Report definition for formatting of Date time?

December 20, 2016 - 10:26am

Currently we are using Pega Expression builder Java function "FormatDateTime" in activity to format the date time in each row of the retrieved results; as an alternative. However this is causing performance impact by adding additional of 10s while retrieving 10000 records. If there had been facility to use User defined java functions in Report definitions to format the columns as we do in activity, then this performance impact would have been reduced and our issue would have resolved.