Support Article
How to turn-off GMT date conversion when using external DB
SA-21973
Summary
User has a stored procedure that returns the availability results for an engineer where one of the column is a time/date value of type DATE.
The external database is Oracle.
The issue they are facing is that when the external database returns a result, PEGA is trying to store it as GMT (UTC).
For example: A value returned 05/04/2016 10:00:00 is stored as 05/04/2016 10:00:00 GMT which is not to the user's expectation.
Because PEGA parsed the datetime as GMT the equivalent is 05/04/2016 11:00:00 BST.
Error Messages
Not Applicable.
Steps to Reproduce
In application, when user calls the external database the results can be seen in the clipboard.
Root Cause
An Oracle DATE data type stores a time or date value without a time zone, as an "unspecified local time".
When such a value is read through a JDBC driver by a Java program (like PRPC), it will be interpreted as it would have the time zone, that is valid for the reading JVM. In the current case, the time or date values in the database were intended to have the time zone Europe or London, while the reading PRPC server had the time zone setting UTC. As long as no daylight savings time was active, the time for Europe or London was GMT (UTC +00:00) and no problem was visible. That changed on 2016-03-27, when DST got activated in the EU; now the reported behavior got active.
Resolution
User had to add an adjustment step for the time or date values that are read from a DATE column.
In this case, user added it to the SELECT statement in the SQL Connector rule, by transforming the contents of the DATE column to a String in ISO 8601 format with a time zone (like YYYY-MM-DD'T'hh:mm:ss.sss TZ), where the time zone should be "Europe or London".
This allows PRPC to automatically adjust the time to the correct UTC value.
Published May 21, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.