AdHoc report shows wrong date (one day earlier than DB)
Article ID: 199303
Updated On:
Products
Issue/Introduction
The dates in the Database are 8/1 and 9/1, whereas my Jaspersoft AdHoc report shows as 7/31 and 8/31.
Environment
Release : All Supported Releases
Component : CLARITY JASPERSOFT
Cause
Clarity stores the dates as midnight timestamp, whereas most tools interpret this as the same day, Clarity does some conversions with the date to take into account timezones. For this reason, there can be a +/-1 difference between the dates in the UI and a reporting tool or another tool. See also: 'Midnight rule' - Fields impacted (dates show as one day later)
Resolution
In Adhoc reporting, there is very limited coding you can do, but if you have the domain you want to update, you may be able to write logic in the domain. Assistance on this would be outside the scope of support.
One possible suggestion that could help may be using SET_LOCAL_TIME_ZONE_IN_SQL. See: PostgreSQL time zone is set to the JasperServer JVM time zone instead of user session time zone and causes issue when type casting for more details. Note that this has not been tested by support, so it's recommended to test in non-production
Additional Information
- For Clarity DB timestamp issues: Report Finish date is showing one day later than in Clarity
- See also Finish date of Idea is showing in INV_INVESTMENTS as one day later
- Best Practices for AdHoc Views that references time zone behaviors in Adhoc reporting.
- 'Midnight rule' - Fields impacted (dates show as one day later)
- Jaspersoft / reports known issues with Clarity
Feedback
