CODEWITHSUNDEEP
javajdbcoracle11gtimestampsqldatatypes
Chiseled
Chiseled

Reputation: 2300

Dealing with Oracle's "Timestamp With local TimeZone" datatype in JDBC

I am trying to call a PL/SQL procedure (with the signature mentioned below) using JDBC. It takes a parameter of type TIMESTAMP WITH LOCAL TIME ZONE. I am using Oracle 11g and Oracle 12c Jdbc Driver Version 12.1.0.2.

-- PURPOSE: Takes a timestamp and returns the formatted string
PROCEDURE deal_With_TimeStamp(intime TIMESTAMP WITH LOCAL TIME ZONE , outTimeString OUT VARCHAR2)

I have written a Java method a that calls the above procedure using JDBC. I eventually want to expose this method in a web service.

I am trying to understand how does one deal with a parameter of type TIMESTAMP WITH LOCAL TIME ZONE in such scenarios.

From what I have read it seems like TIMESTAMP WITH LOCAL TIME ZONE will have all Timestamps normalized based on the timezone in which the database instance operates.It will discard the client timezone information (when supplied) , once it is taken into account for the normalization process. So if the client requests for this info they will have to pass their timezone info again along with the request.

In such a case should I be accepting both timestamp and timezone from a web service client ? This way the info stored in the database will be accurate.

What happens when no timezone information is sent ? Is there a way to set the timezone for a Jdbc connection ? One can pass that to the database in absence of a client's timezone information.

Here is the relevant parts of my Java method that uses JDBC.

 public void dealWithTimeStamp(Timestamp currentTimestampValue , String clientTimeZone)
 {
     Connection conn = null;
     CallableStatement callStmt = null;

     // Create a Database connection
     conn = DriverManager.getConnection(DB_URL , DB_USER , DB_PWD);

     // Create a query string
     String callProc = "{call HR.EMP_PKG.deal_With_TimeStamp ( ? , ?) }";

     // Create a Callable statement
     callStmt = conn.prepareCall(callProc);

     // Bind values to the IN parameter
     callStmt.setTimestamp(1, currentTimestampValue , Calendar.getInstance(TimeZone.getTimeZone(clientTimeZone)));

     // Register OUT parameters
     callStmt.registerOutParameter(2, java.sql.Types.VARCHAR);

     // Execute the Callable Statement
     callStmt.execute();

     // Retrieve the values from the OUT parameter
     String curTimestampStr = callStmt.getString(2);
     System.out.println("Current Timestamp : " + curTimestampStr);   
 }

Please note I have striped the try catch blocks , cleaning up of database resources for the sake of readability:

Upvotes: 0

Views: 1924

Answers (1)

John Bollinger
John Bollinger

Reputation: 180201

From what I have read it seems like TIMESTAMP WITH LOCAL TIME ZONE will have all Timestamps normalized based on the timezone in which the database instance operates.It will discard the client timezone information (when supplied) , once the normalization is done.

Yes, the docs specify that this data type holds only the timestamp itself, not the timezone fields. It differs from TIMESTAMP in that incoming values are converted from the client session time zone to the database time zone, and outgoing values are converted the other direction.

So if the client requests for this info they will have to pass their timezone info again along with the request.

It depends which "client" you mean. The web service host is client to the database. Its time zone is a property of the database session, so you don't have to explicitly "pass" it at all.

If you mean the web service client, however, AND if the web service client is expected to submit and receive timestamps relative to its own local time zone that must be interpreted as such, then the web service must convert timestamps between its client's zone and its own zone where the corresponding Oracle data type is TIMESTAMP WITH LOCAL TIME ZONE. For that to work, the web service client must provide its time zone to the web service.

I would be uncomfortable with modifying the session properties of your database connections according to the characteristics of the web service client. That seems prone to errors. If you must deal with time zone issues then I suggest you approach the problem by standardizing the time zone:

  • Let the web service client interface specify that timestamps are to be given and returned in UTC. The web service then has to worry only about converting between one pair of time zones. It might achieve that semi-automatically by setting the session time zone to UTC (via the ALTER SESSION statement), once per connection, or it might do it in Java.
  • Optionally, change the database to use plain TIMESTAMPs, which are understood to be expressed in UTC. Then you don't need to worry about time zones at all (in combination with the previous).

There is no standard sense of a connection time zone in JDBC, but connections do have generic "client properties". It is possible that the Oracle driver exposes the session time zone as such a property, in which case you could set it via Connection.setClientProperties(). Again, I would personally avoid doing that based on web service client data, but I would be ok with doing it based on the web service host.

Upvotes: 1

Related Questions