Localizing Reports in TIBCO JasperReports® Server with respect to time zones

I have recently worked on a project where database times are stored in UTC but the users work in their local time zones. The problem was one which is familiar to many, namely how to get the reports to show dates in the user’s local time zone in a nice configurable way so that the report templates don’t have to change when they are deployed in a new time zone.

Whilst investigating this I found 2 things:

  1. That there is a second issue which is how to get dates provided as parameters to reports to convert from the user’s time zone into the database time zone so that the correct data is returned

  2. That there is some documentation covering the various issues but it seems to be in different places and some of it seems to hint at solutions rather than explicitly state them

With regards to point 2, the number of questions that have been asked on JasperForge regarding this seems to back up my feeling that the information given is not the most helpful. This is a shame because the functionality provided does the job perfectly well in a way that is simple to configure.

What I want to do here is document the steps required and to provide a brief explanation as to why they are necessary.

There are 3 things that Jasper needs to know in order to parse and display dates correctly:

  • The server time zone

  • The database time zone

  • The user time zone

In many cases the server and user will be in the same time zone, but this is not necessarily the case. Databases are often in UTC but, again, this is not necessarily the case.

The following scenarios need to be handled:

  1. Getting data from the database for display in reports

  2. Entering dates as parameters to reports – often by calendar input controls

In both of these scenarios the user will expect to see dates in their local time zone, so conversions will need to be made if either the server or database is in a different time zone.

For (1) the conversion is from the database time zone to the user’s time zone.

For (2) the conversion is from the server’s time zone to the database time zone. This does result in one potentially confusing scenario which I have yet to find a solution to and which I will return to later.

When the required settings are in place then these conversions are handled natively within Jasper. Note that everything I am doing here assumes that reports are stored in and run from a Jasper Server. I have not investigated how this would work when running reports without using the Server.

Note also that I have been working on Windows so translate what follows as appropriate for your OS.

  1. Setting the server time zone:

    This is achieved by setting the Duser.timezone in JAVA_OPTS. To do this add the following line to your application server start up script:

    set JAVA_OPTS=%JAVA_OPTS% -Duser.timezone=<server_timezone>

    NB: the time zone provided must be from the list of tz timezones: http://en.wikipedia.org/wiki/List_of_tz_database_time_zones

    Things like GMT, CET, EST, whilst in common usage, are not recognized as time zones in this context.

  2. Setting the database time zone:

    This is achieved by setting the time zone when creating a data source. The list of time zones available is configured in: ..\WEB_INF\applicationContext.xml in the bean with id=”userTimeZonesList”. Add any required time zone to this list – e.g. <value>Etc/UTC</value> for UTC.

  3. Setting the user’s time zone:

    This can be achieved in a few ways.

    • If logging in directly to the server then select the time zone from the list on the login page. The list of time zones is configured in the same way as in (2)

    • If running reports via the rest_v2 service then add the parameter userTimezone to the URL.

      e.g. &userTimezone=Africa/Johannesburg

    The value provided here gets set as the REPORT_TIME_ZONE parameter.

    It might be possible to set the user’s time zone when logging in via some SSO mechanism but I have not investigated this.

After changing the server time zone or adding new a time zone to the list you will need to restart your server.

Once these are all set correctly your dates should all be converted without having to change any report templates.

NB – this does assume that your reports are displaying dates and not some string representation of a date.

Confusing scenario mentioned earlier:

If you have a user in a certain time zone who is running reports on a server which is in a different time zone then there is the potential for some confusion. This is because any dates that they enter as parameters will be interpreted as being in the server’s time zone.

e.g. suppose your server is in Europe/London but you have a user in America/New_York who is running reports against that server. Most of the year there is a 5 hour difference between the two, so if the user enters parameters to get data between, say, 1st June and 5th June inclusive then they could reasonably expect to see data that occurred between 1st June and 5th June inclusive in their time zone. But if they enter these values then they will be interpreted as being in the server’s time zone which is 5 hours ahead and so, from the point of view of the user in New York, the dates actually provided to the query engine will be 31st May 19:00 to 5th June 19:00 and the data returned may well be incorrect.

I have yet to investigate possible solutions to this as we do not expect the situation to arise with our application, but if anyone does have a solution then please do update this.

Please remember that REPORT_TIME_ZONEs are applied after data has been retrieved from the database. If you don't set your time zone on quering than you could end up displaing wrong dates, eg see this vToday

SELECT current_setting('TimeZone'),DAY FROM public.vtoday;
 
RETURNS
 
Europe/Berlin    2015-04-20
 
BEGIN;
SET LOCAL TimeZone='Pacific/Enderbury';
SELECT current_setting('TimeZone'), DAY FROM public.vtoday ;
COMMIT;
 
RETURNS
 
Pacific/Enderbury    2015-04-21

Comments

Just a small note that can help other people reading the article posted above. Under the section 3. Setting the user’s time zone it is written following by the author:

"The value provided here gets set as the REPORT_TIME_ZONE parameter. 

It might be possible to set the user’s time zone when logging in via some SSO mechanism but I have not investigated this."

If you are making the build yourself from the source code, you can override it from file jasperreports.properties located under: jasperserver\jasperserver-war\src\main\webappAdditions\WEB-INF\classes.

Just uncomment last line property net.sf.jasperreports.jdbc.time.zone and set your time zone instead of REPORT_TIME_ZONE value (use quotes to ensure unescaping): 

# use the report/user time zone to fetch values from databases
#net.sf.jasperreports.jdbc.time.zone=REPORT_TIME_ZONE
 
Example:
net.sf.jasperreports.jdbc.time.zone='Europe/Belgrade'

 

 

darkolakovic

The Subject discussed earlier: Do we have any solution in Jaspersoft latest version. Need your support

Feedback
randomness