Jump to content
  • Database stores Times in UTC, but reports using this data that are run in JasperReports Server show in the wrong Time Zone


    bnee_1
    • Features: Data Sources, JasperReports Server, Localization, Reports Version: v6.0 Product: JasperReports® Server

    Issue Description

    I am using an Oracle database for my reports, and one of the time fields is returning the wrong values when I run the report in my local time zone.

    The times are stored in UTC in the database, and I am located in CET (Winter)/CEST (Summer).

    The JasperReports Server is installed in CET/CEST, and I choose the time zone on the JasperReports Server login page as Europe/Paris. The data source configuration in JasperReports Server has "Use database settings" for the time zone. The data type for this field is TIMESTAMP.


    Resolution

    CET is UTC+1 in the Winter, and CEST is UTC+2 in the Summer. Since you specified "Use database settings", then the TIMESTAMP values coming from the database will be trusted, and JasperReports Server will internally treat these values as the same time zone as JasperReports Server (CET/CEST).

    So, if the TIMESTAMP value from the database has 12:37:00, then JasperReports Server treats this value as 12:37:00 CET/CEST. So, if you select CET as the time zone on the login page, and it's Winter, you will see 12:37:00 in your report.

    The behavior would differ if the field's datatype was TIMESTAMP WITH TIME ZONE. Then, if you specified "Use database settings" for the data source setup,  the TIMESTAMP WITH TIME ZONE values coming from the database would have a time zone value in them and JasperReports Server would convert them to the time zone JasperReports Server is in (CET/CEST).

    So, 12:37:00 UTC in the database would be treated as 13:37:00 CET or 14:37:00 CEST in JasperReports Server. Then, if you login with CET time zone, you'd see 13:37:00 in the Winter. For this scenario, you could do either of the following:

    1. Change the field's data type to be TIMESTAMP WITH TIME ZONE and leave "Use database settings" for the data source's time zone property

    2. Leave the field's data type as TIMESTAMP and change the data source's time zone to be "UTC".


    Ref. Case 00054352


    User Feedback

    Recommended Comments

    There are no comments to display.



    Guest
    This is now closed for further comments

×
×
  • Create New...