Deal With Time Values from Different Timezones in Webservice

The Scenario

A web service can be used by the users from all over the world. It is very common that the service will need to read and save the time when the user requests the service. Since the users can be from different timezones from the server, how can the time value be transmitted correctly?

Also the application server that hosts the web service and the database server that saves the data may be at different timezones. So this issue also needs to be taken into consideration.

A way to deal with the time values from different timezones

The following is a way to implement the code so the time values are persisted correctly. It is from an example using the Spring Web Service.
To make the whole thing work, there are several pieces.

1. In the message schema or the wsdl file, use dateTime as the type for the time value. An example is the following:
   

   <element name="createTime" type="dateTime" />
   <element name="updateTime" type="dateTime" />

2. In the RPC client generated by Weblogic, the java.util.Calendar is used as the data type for the time to send the request. The following is an example:
   

   request.setCurrentDateTime(Calendar.getInstance());
   

3. In the soap request xml message sent to the web service, the java Calendar data is converted to a text string. The text string will contain the timezone offset. The following is an example of such message:
   

   <aud:currentDateTime>2010-12-14T16:34:27.421-05:00</aud:currentDateTime>

4. The server will get this time value and convert this to an object of java type. In Spring web service, if xml marshalling is used, the xml data will be converted to an object of the type javax.xml.datatype.XMLGregorianCalendar. You can convert this to java.util.Date using the following code:
   

   xmlCal.toGregorianCalendar().getTime();
   

   This date value will keep all the original time information from the user including the timezone offset.
5. Now you can do your java programming on the date value as usual using jdbc, etc. And the time value will be saved correctly.

There is still one important issue on the database side. The date or timestamp data type of a database may not support timezone. For example, the Oracle DATE data type does not store the timezone information. So say you have a time object with the time value of 2009-03-17-22.05.37.569000. This value can actually represent different time values depending on the timezone. But if you use JDBC to save this value, the database will just take this value as is. And the timezone that the database uses for this time will be the timezone of the database server. And this is wrong!

The following is one example. The conditions are these.

1. The oracle database server is using the EST time. ( You can use the query "select sysdate from dual" to check this.)
2. The java program is running on a linux machine which uses GMT time.
3. The database table has a colum called UPDATED_DT with the data tape DATE.
4. The java code uses the following for JDBC to update the UPDATED_DT column:
   

   String sql = "UPDATE foo set updated_dt = ? where ...";
   Timestamp ts = new Timestamp(System.currentTimeMillis());
   pstmt.setTimestamp(1, ts);
   ...
   System.out.println("Timestamp is " + ts);" 
   

The jar ojdbc6.jar is used for the oracle database driver. After running the program, we get the following results:

• On the database, the query "Select * from foo where ..." returns the value for the column UPDATED_DT as
  

  1/12/2012 4:39:07 PM

• The java program prints out the following time value:
  

  Timestamp is  2012-01-12 16:39:07.536

Notice that this is GMT time. The EST time should be 2012-01-12 11:39:07.536.

• Now run a java program on a machine that is using EST time to print out the UPDATED_DT value. The code is the following:
  

  String sql = "SELECT updated_dt FROM foo where ...";
  ...
  Date updatedDt = rs.getTimestamp(2);
  System.out.println("Updated_dt=" + updatedDt + " Timezone Offset=" + updatedDt.getTimezoneOffset()/60);
  

  It prints out the following:
  

  Updated_dt=2012-01-12 16:39:07.0 Timezone Offset=5

You can see that the UPDATED_DT column has been updated with the GMT time. But when retrieving the value, the JDBC result shown is in EST time, which is wrong!

How to fix this problem? There are two ways. The first way is to change the java code. The second way is to use the new data type feature in Oracle. Using the new Oracle data type will be a better solution if it works because then the programmer won't need to worry about what timezone the servers are using.

To use the java code, one can simply apply the following API of the JDBC class PreparedStatement:

/**
     * Sets the designated parameter to the given java.sql.Timestamp value,
     * using the given Calendar object.  The driver uses
     * the Calendar object to construct an SQL TIMESTAMP value,
     * which the driver then sends to the database.  With a
     *  Calendar object, the driver can calculate the timestamp
     * taking into account a custom timezone.  If no
     * Calendar object is specified, the driver uses the default
     * timezone, which is that of the virtual machine running the application.
     *
     * @param parameterIndex the first parameter is 1, the second is 2, ...
     * @param x the parameter value 
     * @param cal the Calendar object the driver will use
     *            to construct the timestamp
     * @exception SQLException if parameterIndex does not correspond to a parameter
     * marker in the SQL statement; if a database access error occurs or 
     * this method is called on a closed PreparedStatement
     * @since 1.2
     */
    void setTimestamp(int parameterIndex, java.sql.Timestamp x, Calendar cal)
     throws SQLException;

Still using the example before, we just need to add one line and then use the above API to update the UPDATED_DT column:

Timestamp ts = new Timestamp(System.currentTimeMillis());
 Calendar cal = Calendar.getInstance(TimeZone
     .getTimeZone("America/New_York"));
 pstmt.setTimestamp(1, ts, cal);

Then when you run the code on a machine that is using GMT, the time value saved into the database will be in EST time.

To use the Oracle to correct the problem, we need to use the database data types that support timezone. For example, Oracle supports following data type:

• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

For additional information, see [1] and [2].

The Precision Of The Time Value

In a programming task I worked on, the requirement is to update a table in DB2 database. The table has a column called UpdateTime. Its column data type is TIMESTAMP. A record is first read from the table. Then some column values are modified. And the record in the table needs to be updated with the modified data. But this updating can only be done when the UpdateTime value has not been changed since the record was read. This is to make sure that no other people has modifed the same record. First of all, I have to say that this is not a good way to update the record. A better way is to create a new column, say LockVersion, to hold an integer value for the version number of the record. The version number will be incremented everytime the record is updated. To use the UpdateTime to keep track of the version is a bad idea. That said, I did investigate the problem if UpdateTime should be used. And I found some interesting results in order to make this thing work. Basically the SQL to update the record will be like the following:

update t set x = ?, UpdateTime = current timestamp where UpdateTime = ?

where you will supply the first "?" with the new value of x, and the second "?" with the UpdateTime value that you already retrieved. Unfortunately most likely this query won't work. The two time values have to match exactly to be equal. For example, the time 2010-12-21T14:20:31:354768 is not equal to 2010-12-21T14:20:31:354767999. However, the extra zeroes at the end are OK. For example, 2010-12-21T14:20:31:354768 is equal to 2010-12-21T14:20:31:354768000. So the key here is how to keep the precision of the time value. A DB2 timestamp column has the microsecond precision. An Oracle timestamp column has the nanosecond precision. In the programming world, the precisions are the follows:

• The java.util.Date has the millisecond precision.
• The java.sql.Date (a subclass of java.uitl.Date) also has the millisecond precision.
• The java.sql.Timestamp(a subclass of java.util.Date) has the nanosecond precision.
• The java.util.GregorianCalendar has the millisecond precision.
• The javax.xml.datatype.XMLGregorianCalendar can have optional infinite precision fractional seconds.

You can use JDBC resultSet.getTimestamp("UpdateTime") to read the time value. Then this data will go through the following phases through the web service.

1. The java time object will be converted to an XML string and sent to the client in a SOAP message.
2. The client will convert the time value in XML string to an java object( or other objects if different programming languages are used). The client will then do some business on the record.
3. The client converts the time object to an XML string, and send a SOAP message to the server to update the record.
4. The server converts the XML string in the SOAP message received from the client to a java object and then call the SQL to update the record.

In all the above steps, if any step loses the precision of the time value, the condition "UpdateTime = ?" in the SQL won't succeed and the update will not happen. An immediate observation is that if you retrieve the UpdateTime and store it in a java.util.Date (Note: not its subclass java.util.Timestamp) object or a java.util.Calendar object, the SQL won't work. This is because the Date class and the Calendar class can only save the time up to millisecond. You lose the microseconds or nanoseconds part of the timestamp in the database. I used Spring web service. The following is a list of the things that can be done to make the whole thing work.

1. In the XML schema for the web service, you can use xsd:dateTime as the type for UpdateTime. This type can accept fractional seconds of any precision. The official definition from http://www.w3.org has the following: A single lexical representation, which is a subset of the lexical representations allowed by [ISO 8601], is allowed for dateTime. This lexical representation is the [ISO 8601] extended format CCYY-MM-DDThh:mm:ss where "CC" represents the century, "YY" the year, "MM" the month and "DD" the day, preceded by an optional leading "-" sign to indicate a negative number. If the sign is omitted, "+" is assumed. The letter "T" is the date/time separator and "hh", "mm", "ss" represent hour, minute and second respectively. Additional digits can be used to increase the precision of fractional seconds if desired i.e the format ss.ss... with any number of digits after the decimal point is supported.
2. Database to Java. When you read the UpdateTime from the database, use resultSet.getTimestamp("UpdateTime") and save it into a java.util.Timestamp object.
3. Java to XML. Use the following to convert the java.util.Timestamp to XMLGregorianCalendar
   

   public static XMLGregorianCalendar toXMLGregorianCalendar( Timestamp timestamp) throws DatatypeConfigurationException {
     java.util.GregorianCalendar c = new java.util.GregorianCalendar();
     c.setTime(timestamp);
     int fractionalSeconds = timestamp.getNanos();
     XMLGregorianCalendar xmlCal = DatatypeFactory.newInstance()
      .newXMLGregorianCalendar(c);
     xmlCal.setFractionalSecond(new BigDecimal(fractionalSeconds + "E-9")); 
     return xmlCal;
    }
   

   Spring will convert the XMLGregorianCalendar to the xml string and send the SOAP message to the client.
4. XML to Java.(Client side) On the client side of the web service, if it is java, the java.util.Timestamp should be used. Note that usually you will use some tool to generate the artifacts for the web service client from the WSDL file. I noticed that the RPC client generated by weblogic uses java.util.Calendar for the xsd:dateTime value. I do not know if there are other options that you can change this. But this client won't work because the java.util.Calendar class can only store milliseconds. We need the ability to store microsecond or nanosecond here.
5. XML to java.(Server side) After receiving the SOAP message from the client, Spring will convert the time string to an XMLGregorianCalendar object. You can use the following to convert XMLGregorianCalendar to java.util.Timestamp:
   

   public static Timestamp xmlGregorianCalendarToDate(XMLGregorianCalendar xmlCal) {
     Timestamp timestamp = new Timestamp(xmlCal.toGregorianCalendar()
      .getTimeInMillis());
     BigDecimal fr = xmlCal.getFractionalSecond();
     BigDecimal fr2 = fr.multiply(new BigDecimal("1E9"));
     timestamp.setNanos(fr2.intValue());
     }
     return timestamp;
    }
   

6. Java to database. Now you can use the Timestamp object to do the business and use this in JDBC to update the record. If you use the org.springframework.jdbc.object.SqlUpdate class in Spring JDBC, the parameter type for the UpdateTime can be defined as following:
   

   declareParameter(new SqlParameter(Types.TIMESTAMP));

The Spring web service also has a way to code the web service client. I used it and successfully updated the record.

References

[1] http://mail-archives.apache.org/mod_mbox/openjpa-users/200903.mbox/%3C49C082FC.9070501@jotobjects.com%3E
[2] http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CDEGDFFE