ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

AdHoc report shows wrong date (one day earlier than DB)

book

Article ID: 199303

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

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.

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)

Environment

Release : All Supported Releases

Component : CLARITY JASPERSOFT

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