title: Dealing with Oracle's "Timestamp With local TimeZone" datatype in JDBC
body: <p>I am trying to call a PL/SQL procedure (with the signature mentioned below) using JDBC. It takes a parameter of type <code>TIMESTAMP WITH LOCAL TIME ZONE</code>. I am using <code>Oracle 11g</code> and <code>Oracle 12c Jdbc Driver Version 12.1.0.2</code>.</p>
  
  <pre><code>-- PURPOSE: Takes a timestamp and returns the formatted string
  PROCEDURE deal_With_TimeStamp(intime TIMESTAMP WITH LOCAL TIME ZONE , outTimeString OUT VARCHAR2) 
  </code></pre>
  
  <p>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. </p>
  
  <p>I am trying to understand how does one deal with a parameter of type <code>TIMESTAMP WITH LOCAL TIME ZONE</code> in such scenarios.</p>
  
  <p>From what I have read it seems like <code>TIMESTAMP WITH LOCAL TIME ZONE</code> 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.  </p>
  
  <p><strong>In such a case should I be accepting both timestamp and timezone from a web service client ?</strong> This way the info stored in the database will be accurate. </p>
  
  <p><strong>What happens when no timezone information is sent ? Is there a way to set the timezone for a Jdbc connection ?</strong>  One can pass that to the database in absence of a client's timezone information. </p>
  
  <p>Here is the relevant parts of my Java method that uses JDBC.</p>
  
  <pre><code> 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);   
   }
  </code></pre>
  
  <p><strong><em>Please note I have striped the try catch blocks , cleaning up of database resources for the sake of readability</em></strong>:</p>