Question
Capgemini India
IN
Last activity: 27 Dec 2016 5:00 EST
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.
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
Accepted Solution
Capgemini India
IN
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.
Pegasystems
IN
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.
Capgemini India
IN
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".
Capgemini India
IN
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.
Pegasystems
IN
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.
Capgemini India
IN
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.
Accepted Solution
Capgemini India
IN
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.
Capgemini India
IN
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?
Capgemini India
IN
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.