Support Article
Reporting Date Timezone incorrect, dates off by one day.
SA-19760
Summary
Customer created a custom report including a Date and Time property to be presented as a plain Date without the time part.
The Application Server and the Operator time zone are in sync with a positive offset from Coordinated Universal Time (UTC).
E.g.: +01:00 or Europe/Berlin
When the report is executed the date value is always one day behind the original date.
Error Messages
No error message.
Steps to Reproduce
- Define a Date and Time Property in the Data Model
- Edit the Cell Property of the Section rule specifying a Date mode setting in the Presentation Tab
- Create a Report Definition for the Work Object including the Date property and specify a Date format in the Column Options
- Create a new Work Item and enter a valid date in the Calendar Control then submit the Work Item.
- Run the report previously created
- The date field is one day behind the original value.
Root Cause
A defect or configuration issue in the operating environment:
Briefly this is due to having used a Date Time property to represent a Date value.
A Date property would have been more appropriated in this case avoiding the occurrence of this issue.
More in detail the root cause is the particular combination of HTML controls used to assign a Date value to the Date Time property in the Work Item and for showing this property as date in the Report Definition.
Using the Calendar control in the section with Date mode means assigning 00:00 to the time part of the Date Time property.
With the time zone conversion being involved in this case the date time property is shifted to the day before.
E.g.. the date 20160101 in the Europe/Berlin time zone is converted to 20151231T230000.000 GMT
This is normally not an issue with Date Time properties as the opposite conversion happens when the date is retrieved from the database.
However in this particular case the time part is truncated by the selected HTML control used in the report definition.
This truncation prevents Pega to perform the reverse time adjustment from GMT to the corresponding Operator time zone.
This can be easily proved by removing the column format which will revert the report to use the default HTML Control for the date and time property.
Resolution
Perform the following local-change:
Use the appropriate property type whenever is possible to avoid the risk of incurring in this issue.
Assuming you cannot change your Data Model (which would be more correct) there are two mutually exclusive ways to work-around this issue:
- Create a custom HTML control to render the Date Time property as a Date and include this custom control in the Report Definition.
- Use a SQL function alias rule to adjust the SQL Date to the Application Server time zone.
from: sDatetime = PRDateFormat.format(null, strTimeZone, PRDateFormat.DEFAULT_DATETIME_SHORT, curProp.toDate());
to: sDatetime = PRDateFormat.format(null, strTimeZone, "dd.MM.yyyy", curProp.toDate());
Adopt the Date pattern that best suits your needs.
The following screen shot depict the modified HTML Control with the current change highlighted:
The second solution albeit viable is less desirable as it depends on the database in use.
Published February 16, 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.