Time Zone for Oracle JDBC Driver

In my last post - Time Zone for af:convertDateTime, I introduced how the date values are passed around in an typical ADF application, and specifically, how the ADF Faces handles the date values conversion with respect to the time zone configuration. To review it, let's take a look at this figure again:

In this post I will be talking about another part of the puzzle - how the Oracle JDBC driver processes date values with regards to time zones. It can be illustrated as the following simple figure:

As shown in the figure, this post will use java.sql.Date (or simply Date in monospace type) and the Oracle DATE datatype (or simply DATE in monospace type) for the discussion. The term "date value" will be used for general purposes.

Oracle Database stores date values in its own internal format. A DATE value is stored in a fixed-length field of seven bytes, corresponding to century, year, month, day, hour, minute, and second. When a date value goes from the application to the database; and out of the database back to the application. It works like this, basically:

1. A java.sql.Date value is created to hold the date value, and it's in the time zone GMT.
2. The Date value is sent to the Oracle JDBC Driver, and the driver converts it to the Oracle DATE value and passes it to the database.
3. The Oracle JDBC Driver retrieves the DATE value out of the datbase, converts it back to the java.sql.Date value.

The Java Date value carries the time zone information implicitly which is always GMT by definition; but the Oracle DATE datatype does not. For Oracle JDBC Driver to convert the value between these two datatypes, another time zone must be specified in some way as the source or destination time zone. If you just want a quick answer, here is it: Oracle JDBC Driver will use the default time zone of the Java VM if it's not explicitly specified.

The key lies in the class oracle.sql.Date, which provides conversions between the Oracle DATE datatype and the Java java.sql.Date (and java.sql.Time, java.sql.Timestamp). Specifically, I'll talk about its two overloaded methods used to convert the Oracle DATE value into the Java Date value. The reverse conversions are handled by its constructors with the same ideas shared.

One of methods is:

public static Date toDate(byte[] date, Calendar cal)

And another one is:

public static Date toDate(byte[] date)

Calling the second one is simply equivalent to call toDate(date, null). Let's focus on the first one. This method accepts two parameters. The first parameter represents the Oracle DATE value to be converted with each byte in the array corresponding to each field in the internal format of the Oracle DATE datatype (that seven-byte, fixed-length format). For the other parameter, it's documented as this:

cal - Calendar which encapsulates the timezone information to be used to create Date object

Here is how this method works:

1. A new Calendar instance is created using the TimeZone encapsulated in the specified Calendar parameter (cal1 = Calendar.getInstance(cal.getTimeZone())). In case the Calendar parameter is null, use the default time zone (cal1 = Calendar.getInstance()).
2. Populate each field of the new Calendar instance with the value of each corresponding field in the byte array.
3. Create and return a new java.sql.Date object using the long value of the time returned from the populated Calendar instance (new java.sql.Date(cal1.getTime().getTime())).

In summary, Oracle JDBC Driver interprets the date values retrieved from the database as in the time zone of the Java VM by default. The values that are actually loaded into the Java Date values may vary depending on your Java VM, and vice versa.

Series on Time Zone

• Configuring the Time Zone with WebLogic Server
• Time Zone for af:convertDateTime
• Time Zone for Oracle JDBC Driver

Environment

• JDeveloper 12.1.3.0.0 Build JDEVADF12.1.3.0.0GENERIC_140521.1008.S
• Oracle Database 12.1.0
• Oracle JDBC 12.1.0
• Mac OS X Version 10.10

Resources

• DATE (Oracle Database JDBC Java API Reference)
• ResultSet (Java Platform SE 7 )
• Calendar (Java Platform SE 7 )

ADF - Time Zone for af:convertDateTime

In the last post about configuring WebLogic Server time zone, I mentioned one of reasons you do it is to configure the default time zone for ADF Faces to convert date and time for input and output components. This post will focus on it - how the ADF Faces convertDateTime converter and the af:convertDateTime tag work with the time zone configuration in detail. This is only the first piece of the puzzle. Hopefully, I can put other pieces together to complete the puzzle with another two or more subsequent posts.

Here you can find the Source Code of the sample application or Download ZIP of it.

To make it easier, I'm using java.util.Date in the discussion and the sample application, and Oracle data type DATE in some figures. The basic idea applies to java.sql.Date, etc.

In Java, the class java.util.Date represents a specific point in time. As per the javadocs for one of its constructors - Date(long date):

Allocates a Date object and initializes it to represent the specified number of milliseconds since the standard base time known as "the epoch", namely January 1, 1970, 00:00:00 GMT.

Clearly, the class Date represents a determinate point in time which is in the time zone GMT. To display a Date, we need a converter or a formatter to turn the Date into a String which represents the "wall clock time" local to a specific time zone. When the target time zone changes, the resulting String or the "wall clock time" could change, but the value of the Date does not change in this process.

The following figure illustrates how the date and time data passes through a typical ADF application:

• The ADF Faces component accepts the user input as a String value and convert it into a Java Date value with a DateTimeConverter.
• The Oracle JDBC driver passes the Date value into the database as an Oracle DATE value.
• For output, the JDBC driver retrieves the Oracle DATE value out of the datebase as a Java Date value.
• The ADF Faces component display the date and time after converts the Java Date value into a String value with a DateTimeConverter.

I'll talk about the JDBC part in my next post, and here will focus on how the time zone configuration comes into play in the view part:

When an ADF Faces component works with a DateTimeConverter, a java.util.TimeZone object can be configured with it, as shown in the following code snippet from the sample application:

<af:inputText 
        id="it_dt"
        label="Date Time: "
        value="#{userBean.dateTime}" autoSubmit="true">
    <af:convertDateTime 
        pattern="yyyy-MM-dd HH:mm" 
        timeZone="#{userBean.inputTimeZone}"/>
</af:inputText>

Here's the description from ADF RichClient API - <af:convertDateTime> for the timeZone attribute:

Time zone in which to interpret any time information in the date string. If not set here, picks this value from trinidad-config.xml configuration file. If not defined there, then it is defaulted to the value returned by TimeZone.getDefault(), which is usually server JDK timezone.

When the component is used for user input, the TimeZone object specifies the source time zone in which the date string should be interpreted, and convert the String input value into a Date value which is in the destination time zone GMT. When the component is used for output, the TimeZone object specifies the destination time zone, and the Date value is converted into a String representing the local date and time in the time zone specified by the TimeZone object.

You can configure the time zones in three levels:

• System-level time zone
• Application-level time zone
• Converter-level time zone

The system-level time zone can be configured as described in my last post - Configuring the Time Zone with WebLogic Server. The application-level time zone can be configured like this as in the sample application:

<trinidad-config xmlns="http://myfaces.apache.org/trinidad/config">
  <time-zone>#{applicationBean.applicationTimeZone}</time-zone>
</trinidad-config>

The converter-level time zones can be configured with business-specific time zones or user preference time zones according to your application requirement. For example, in an application displaying a flight's departure time and arrival time, two different time zones for the departure airport and arrival airport respectively can be used. That's the business-sepcific time zone approach. You can also support the user preference time zones in this case as an user-friendly feature.

This post covers how the time zones participate in the date values processing in the ADF Faces view layer. In the next post, I'll introduce what happens when the date values are accessed with the Oracle JDBC driver.

Special Note for the ADF prior to 12c

In the ADF 11g, the timeZone attribute of the af:convertdateTime is documented as this:

Time zone in which to interpret any time information in the date string. If not set here, picks this value from adf-faces-config.xml configuration file. If not defined there, then it is defaulted to GMT.

Series on Time Zone

• Configuring the Time Zone with WebLogic Server
• Time Zone for af:convertDateTime
• Time Zone for Oracle JDBC Driver

Sample Application

• Source Code
• Download ZIP

Environment

• Oracle Alta UI
• JDeveloper 12.1.3.0.0 Build JDEVADF12.1.3.0.0GENERIC_140521.1008.S
• Safari Version 8.0
• Mac OS X Version 10.10

Resources

• ADF RichClient API - <af:convertDateTime>
• A.6.2.3 Time Zone and Year

Configuring the Time Zone with WebLogic Server

In order to properly handle the date and time data in your ADF applications, you probably need to configure the WebLogic Server time zone, for the reasons including but not limited to:

• Configure the default time zone for <af:convertDateTime> used by input and output components.
• Configure the time zone that affects how the Oracle JDBC driver handles the date and time data.

This post introduces how to configure the time zone with an integrated or a standalone WebLogic Server, or the ADF Model Tester.

Integrated WebLogic Server and ADF Model Tester

When you are running and testing your application using an Integrated WebLogic Server, or testing your model project with the ADF Model Tester you can configure the time zone by adding the following system property to the Java Options on the Launch Settings page in the Edit Run Configuration window:

-Duser.timezone=UTC

To do this:

1. Select the project in the Applications window.
2. From the main menu, choose Application > Project Properties
3. Select Run/Debug.
4. Choose to Edit the selected run configuration (a default run configuration is created for each new project).
5. Add the time zone system property to Java Options

The configuration will apply when the Java program is launched from JDeveloper, for example the Integrated WebLogic Server and the ADF Model Tester. To confirm it, you can look for the system property in the Log window after the program is launched:

Another way to configure the Integrated WebLogic Server time zone is to set it by modifying the properties of the integrated application server:

1. In the Application Servers window, right-click the integrated application server (the default instance is called IntegratedWebLogicServer), choose Properties.
2. Select Launch Settings tab.
3. Add the time zone system property to Java Options

Please note that the Launch Settings of the Application Server Properties are used only when the server starts with no application selected (effectively meaning no application is open in the Applications window).

Caution: when the server starts with no application selected, and then open the application and run it against the server, the Launch Settings defined in the Application Server Properties will be used; the Java Options defined in the run configuration of the project will be ignored.

Standalone WebLogic Server

To configure the time zone with a standalone WebLogic Server instance, if you use a WebLogic Server script to start servers, you can edit the JAVA_OPTIONS in the script to set the system property, see "Specifying Java Options for a WebLogic Server Instance"; if you use the Node Manager to start servers, you can set Java Options for each server instance in the Oracle WebLogic Server Administration Console, see "Set Java options for servers started by Node Manager".

Series on Time Zone

• Configuring the Time Zone with WebLogic Server
• Time Zone for af:convertDateTime
• Time Zone for Oracle JDBC Driver

Resources:

• Specifying Java Options for a WebLogic Server Instance
• Set Java options for servers started by Node Manager
• ADF RichClient API - <af:convertDateTime>