PostgreSQL time zone is set to the JasperServer JVM time zone instead of user session time zone and causes issue when type casting

Scenario:

You have built a report that is run against PostgreSQL database. The report does some type casting of timestamp parameters from the server to Timestamp with Time Zone. Report runs fine when run in Studio but on TIBCO JasperReports^® Server side the timestamp with time zone returned by the database is always is the server time zone regardless of what time zone is picked during user login. For example, your server JVM is in America/New_York time zone, your user is America/Los_Angeles time zone and database is in UTC. The timestamp with time zone returned by the database in this case is always in America/New_York time zone. Why is that and how to avoid this issue?

Solution:

JasperReports^® Server uses PostgreSQL drivers to connect to the database. By PostgreSQL specification, the time zone for the database session established by the driver is always assumed to come from the client. The client time zone in this case will be the time zone of JVM. Regardless of what time zone is picked during the session, the driver will look for the Java time zone to establish the session. The time stamp casting in this case will happen in that assumed time zone.

There are some options on how to deal with that:

• Explicitly specify the time zone when casting to timestamp to tz using type cast functions. This approach, however, might not be
• If you are using JasperReports^® Server 7.2.0 Professional you can download the hotfix_JRSPro7.2.0_cumulative_20200123_0006.zip patch and apply it to the server. The patch adds a new option for the SQL query executer that issues a set time zone directive that sets the session time zone to report time zone before executing the report query. Once installed, you can set the profile attribute on the server level SET_LOCAL_TIME_ZONE_IN_SQL to value True. The profile attribute is set by the superuser in Manage > Server Settings > Server Attributes. It can also be set as a property in JRXML: <property name=“SET_LOCAL_TIME_ZONE_IN_SQL” value=“true”/>. This feature will be introduced in future versions of JasperReports^® Server as well.