Working with Time Zones in

Oracle Business Intelligence Publisher

ORACLE WHITE PAPER | JULY 2014
Table of Contents

Introduction 1

Time Zones in Oracle BI Publisher Reports 2

Converting Dates to the User Preferred Time Zone 2

Preventing Time Zone Conversion of a Date 2

Converting Dates to a Specific Time Zone 3

Time Zone Settings 4

DB Time Zone 4

JVM Time Zone 4

User Preferred Time Zone 5

Time Zones Treatment in Oracle BI Publisher Reports 6

Understanding Time Zone Conversion 6

Understanding How to Prevent Time Zone Conversion 6

Understanding Date Conversion to a Specific Time Zone 7

WORKING WITH TIME ZONES IN ORACLE BUSINESS INTELLIGENCE PUBLISHER

Introduction
Oracle provides a range of features to support customers who conduct business across different time
zones. Time zone settings can influence the values of date and time fields displayed in Oracle
Business Intelligence Publisher (BI Publisher) reports.

Date and time fields are implemented using the following date time data types:

» DATE
» TIMESTAMP
» TIMESTAMP WITH TIME ZONE
» TIMESTAMP WITH LOCAL TIME ZONE

These data types enable consistent information to be stored about the time of events and transactions,
while also supporting the requirement to display values in different time zones.

This white paper describes the following time zone features:

» Time Zones in Oracle BI Publisher Reports: Simple instructions to implement the different
reporting scenarios in Oracle BI Publisher reports without the need to understand the time zone
settings and how they impact the values reported.
» Time Zone Settings: An overview of the time zone settings that impact the value of date time fields
in different reporting scenarios.
» Time Zones Treatment in Oracle BI Publisher Reports: Different reporting scenarios that highlight
the impact of time zone settings.

1 | WORKING WITH TIME ZONES IN ORACLE BUSINESS INTELLIGENCE PUBLISHER

Time Zones in Oracle BI Publisher Reports
There are several ways that you can set the time zones to meet your requirements. The following reporting
scenarios are described with simple instructions on how to address each situation:

» Converting dates to the user preferred time zone

» Preventing time zone conversion of a date
» Converting dates to a specific time zone

Converting Dates to the User Preferred Time Zone

Date fields selected in the data model are automatically converted to the time zone specified in the Oracle BI
Publisher user preferences. This is the default behavior for a date field. You can format the date field using one of
the following format masks:

<?format-date:TRANSACTION_DATE;’XDODEFDATE’?>

<?format-date:TRANSACTION_DATE;’XDODEFDATE XDODEFTIME’?>

For more information on XDODEFDATE and XDODEFTIME, see Implementing Localization Formatting in Oracle BI
Publisher Reports in the Oracle Fusion Applications Developer’s Guide.

Preventing Time Zone Conversion of a Date

To prevent a date from being converted to the user preferred time zone in a report, use the following steps:

1. Convert the date to a string in the data model using one of the following commands:

TO_CHAR(<DATETIME>,‘YYYY-MM-DD’)

TO_CHAR(<DATETIME>,‘YYYY-MM-DD’”T”‘HH24:MI:SS’)

2. In your RTF layout template, use XDODEFDATE or XDODEFTIME to format the date:

<?format-date:TRANSACTION_DATE;’XDODEFDATE’?>

<?format-date:TRANSACTION_DATE;’XDODEFDATE XDODEFTIME’?>

2 | WORKING WITH TIME ZONES IN ORACLE BUSINESS INTELLIGENCE PUBLISHER

Converting Dates to a Specific Time Zone
Use the function FROM_TZ to convert a date field to another time zone. For example, to convert the field
TRANSACTION_DATE from the data base time zone to the time zone of America/Los_Angeles, use the following
command:

FROM_TZ(CREATION_DATE, DBTIMEZONE) at time zone 'America/Los_Angeles'

To prevent the above date from being converted to the user preferred time zone, use the TO_CHAR function. For
example:

SELECT TO_CHAR(

FROM_TZ(CREATION_DATE, DBTIMEZONE) at time zone 'America/Los_Angeles'

,’YYYY-MM-DD’) creation_date

FROM ap_invoices_all

To convert a date to the user preferred time zone, use SESSIONTIMEZONE:

SELECT TO_CHAR(

FROM_TZ(CREATION_DATE, DBTIMEZONE) at time zone SESSIONTIMEZONE

,’YYYY-MM-DD’) creation_date

FROM ap_invoices_all

Note: You do not need to wrap the FROM_TZ with TO_CHAR if you are using the WHERE clause of the data
model’s SQL statement.

For a list of the available time zones, run the following SQL statement:

SELECT *

FROM V$TIMEZONE_NAMES

ORDER BY TZNAME, TZABBREV;

3 | WORKING WITH TIME ZONES IN ORACLE BUSINESS INTELLIGENCE PUBLISHER

Time Zone Settings
There are three time zone settings that influence the reporting of date and time fields:

» Database (DB) time zone

» Java Virtual Machine (JVM) time zone
» User preferred time zone
The following technical details are not required reading to be able to properly set time zones in Oracle BI Publisher
reports.

DB Time Zone
DB time zone is the setting at the database level and the actual time zone in which the date and time fields are
stored by default. For example, the user interface may display the following date field in Pacific Standard Time
(UTC-08:00):

01 January 2014 23:00 PST

In a database with a DB time zone of UTC, the actual date and time stored in the date field is:

02-January 2014 07:00

JVM Time Zone

When running an Oracle BI Publisher report, Java Database Connectivity (JDBC) retrieves the data from the
database and generates the XML. During this process, the date fields in the XML are converted to the java
canonical format.

To convert the date field to the canonical format when a time element or time zone is not included, JDBC adds a
time of midnight (00:00:00) to the time zone of the JVM.

For example, consider the following simple query in a data model:

SELECT TO_DATE('01-JAN-2014', 'DD-MON-YYYY') TRANSACTION_DATE

FROM dual

The generated XML converts the date field to the following canonical format:

<TRANSACTION_DATE>2014-01-01T00:00:00.000-07:00</TRANSACTION_DATE>

4 | WORKING WITH TIME ZONES IN ORACLE BUSINESS INTELLIGENCE PUBLISHER

The inclusion of the time and time zone information could result in an adjustment to the displayed date value when a
user views the report in a different time zone.

User Preferred Time Zone

Oracle BI Publisher enables the user to specify which time zone to use for reports, as well as the date and time
format. For example, a user in Honolulu would want to see their dates and times reported in the UTC-10:00 time
zone.

For reports to display the dates and times in the format defined in the Oracle Fusion Applications preferences, use
the format masks XDODEFDATE and XDODEFTIME. In the report layout, you format the field using the following
command:

<?format-date:TRANSACTION_DATE;’XDODEFDATE’?>

The above date would also be displayed in the user preferred time zone.

Consider the following command in the XML generated by the data model:

<TRANSACTION_DATE>2014-01-01T00:00:00.000-07:00</TRANSACTION_DATE>

A user whose preferred time zone is set to Honolulu UTC-10:00 would see the following value reported, although the
format would depend upon the Oracle Fusion Applications preferences:

Figure 1.Sample output of a user preferred time zone

The date value has been shifted back to the previous day because:

» The JVM appends midnight (00:00:00) and the time zone UTC-07:00 to the value in the XML to convert the date
to the canonical format.
» The date is shifted back an additional three hours into the previous day because the layout displays the dates in
the user preferred time zone of UTC-10:00.
» Three hours is the difference between the user preferred time zone of UTC-10:00 and the time zone
appended to the date in the XML of UTC-07:00.

5 | WORKING WITH TIME ZONES IN ORACLE BUSINESS INTELLIGENCE PUBLISHER

Time Zones Treatment in Oracle BI Publisher Reports
This section discusses in greater depth the different reporting requirements detailed in the section Time Zones in
Oracle BI Publisher Reports and the impact of settings described in the section Time Zone Settings, such as:

» Understanding time zone conversion

» Understanding how to prevent time zone conversion
» Understanding date conversion to a specific time zone

Understanding Time Zone Conversion

Time zone conversion typically follows these steps:
1. Dates in the XML generated by the report’s data model are automatically converted to canonical format
using the JVM time zone.
2. The date field is converted to the time zone specified in the user preferences.
3. The format masks XDODEFDATE and XDODEFTIME determine the format used for the date according to
the Oracle Fusion Applications preferences. For example:

<?format-date:TRANSACTION_DATE;’XDODEFDATE’?>

<?format-date:TRANSACTION_DATE;’XDODEFDATE XDODEFTIME’?>

For more details on the user preferences setting, see Setting Preferences in the Oracle Fusion Middleware User’s
Guide for Oracle Business Intelligence Enterprise Edition.

Understanding How to Prevent Time Zone Conversion

There may be some dates in your report that you do not want to convert to the user preferred time zone. For
example, you may want to see the actual date value displayed in the DB time zone.

Since the JVM will convert date fields in the XML to canonical format, you must convert the date time field to a string
in the data model using the function TO_CHAR. For example:

TO_CHAR(TRANSACTION_DATE,YYYY-MM-DD’)

Note: Using the TRUNC function on a date time field in the SELECT clause will not prevent the JVM from converting
the date time field to the canonical format. Although TRUNC(<DATETIME>) resets the time to 00:00:00, the field is
still a DATE data type. Therefore, you should use the TO_CHAR function instead.

6 | WORKING WITH TIME ZONES IN ORACLE BUSINESS INTELLIGENCE PUBLISHER

You can format the date in the report layout using XDODEFDATE to display the DB time zone and not the user
preferred time zone. To use XDODEFDATE and XDODEFTIME, specific format masks must be used when
converting the date time field to a string. The format masks should be one of the following:

TO_CHAR(<DATETIME>,‘YYYY-MM-DD’)

TO_CHAR(<DATETIME>,‘YYYY-MM-DD’”T”‘HH24:MI:SS’)

Understanding Date Conversion to a Specific Time Zone

There may be situations when you want to convert the dates stored in the DB time zone to another time zone. For
example, a multinational company has a DB time zone of UTC but they have a requirement to report transactions
that were entered during the work day in the Pacific time zone (UTC-:08;00) on 01 January 2014.

For this date conversion, you can use the function FROM_TZ in the following command:

FROM_TZ(CREATION_DATE, DBTIMEZONE) at time zone 'America/Los_Angeles'

This command converts the field TRANSACTION_DATE from the DB time zone to the time zone
'America/Los_Angeles'.

If a transaction has a CREATION_DATE of 02 January 2014 00:01 in the database which is using the UTC time
zone, then the above command would return the date below in UTC-08:00:

01 January 2014 16:01

To display the date in your report, use the following statement in the data model:

SELECT TO_CHAR(

FROM_TZ(CREATION_DATE, DBTIMEZONE) at time zone 'America/Los_Angeles'

,’YYYY-MM-DD’) creation_date

FROM ap_invoices_all

This will result in the following value in the XML which can be formatted using XDODEFDATE in the report layout,
ensuring that no further time zone conversion will take place:

<CREATION_DATE>2014-01-01</CREATION_DATE>

7 | WORKING WITH TIME ZONES IN ORACLE BUSINESS INTELLIGENCE PUBLISHER

To include the command in your WHERE clause and only consider the date and not the time element of the date
time field, you can either use the TO_CHAR or the TRUNC function.

Using TRUNC in the SELECT clause will not prevent a date time field from being converted into the canonical
format by the JVM when creating the XML. However, you can still use TRUNC in the WHERE clause. For example:

SELECT TO_CHAR(

FROM_TZ(CREATION_DATE, DBTIMEZONE) at time zone 'America/Los_Angeles'

,’YYYY-MM-DD’) creation_date

FROM ap_invoices_all

WHERE TRUNC(FROM_TZ(creation_date, DBTIMEZONE) at time zone 'America/Los_Angeles')

= TO_DATE('01-JAN-2014','DD-MON-YYYY')

8 | WORKING WITH TIME ZONES IN ORACLE BUSINESS INTELLIGENCE PUBLISHER

 Oracle Corporation, World Headquarters Worldwide Inquiries
500 Oracle Parkway Phone: +1.650.506.7000
Redwood Shores, CA 94065, USA Fax: +1.650.506.7200

CONNECT WITH US

blogs.oracle.com/oracle Copyright © 2014, Oracle and/or its affiliates. All rights reserved. This document is provided for information purposes only, and the
contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other
facebook.com/oracle warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or
fitness for a particular purpose. We specifically disclaim any liability with respect to this document, and no contractual obligations are
formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any
twitter.com/oracle means, electronic or mechanical, for any purpose, without our prior written permission.

oracle.com Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and
are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are
trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group. 0714