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:
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
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:
Getting data from the database for display in reports
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.
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.
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_INFapplicationContext.xml in the bean with id=”userTimeZonesList”. Add any required time zone to this list – e.g. <value>Etc/UTC</value> for UTC.
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
Recommended Comments