Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

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

  1. Define a Date and Time Property in the Data Model


     
  2. Edit the Cell Property of the Section rule specifying a Date mode setting in the Presentation Tab


     
  3. Create a Report Definition for the Work Object including the Date property and specify a Date format in the Column Options


     
  4. Create a new Work Item and enter a valid date in the Calendar Control then submit the Work Item.


     
  5. Run the report previously created
  6. 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:
  1. Create a custom HTML control to render the Date Time property as a Date and include this custom control in the Report Definition.
  2. Use a SQL function alias rule to adjust the SQL Date to the Application Server time zone.
The custom HTML Control can been created saving the OOTB Export_DateTime as Export_DateTime_Custom and changing line 8 of the HTML source 

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

Was this useful?

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us