Skip to main content

Java TimeZone ++: mapping Calendar to Oracle Date or TimeStamp

In Oracle Database date and timestamp columns can be read in java using s[g]etTimeStamp() methods for storing date and time information. However, they do not save the TimeZone information! The read and write operations are done on java.sql.TimeStamp which is a subclass of java.util.Date. When reading and writing to database, they just write out the *face value* (read my previous post on dates for explaination) in Java VM's default TimeZone the code is running on. And read the value too 'TO' the java VM's Default TimeZone. So assuming you WriteDate program on a VM in EST and ReadDate program on another VM in PST (or just change system timezone) - You are getting a different *value* in the date Object.

So, How do you tackle this issue. The best way is to set your application to run on a specified (constant) default timezone. As mentioned in the earlier post this can be achieved by TimeZone.setDefault(). If you are unable to do it (For reasons unknown to me, so far), JDBC specification allows sql dates to be read and written using a specified calendar. The resultSet.s[g]etTimeStamp method has an overloaded cousin, that takes a Calendar parameter. These methods save the date *face value* into the db after converting it to the timezone of the calendar passed as argument. So if there is a central place, You would want to have code some thing like this.
static final Calendar networkCal = Calendar.getInstance(TimeZone.getTimeZone("EST"));

 //Writing somewhere
 PreparedStatement ps = conn.prepareStatement("Update datex set dt = ?, tms = ?, faceval =? where id=1");
 ps.setDate(1, new java.sql.Date(userDate.getTime()));
 ps.setTimestamp(2, new Timestamp(userDate.getTime()), networkCal);

 //Reading Elsewhere
 ResultSet rs = st.executeQuery("select * from datex where id = 1");
 sqlTS = rs.getTimestamp("tms", networkCal);

In iBatis (we use iBatis), You can have a TypeHandlerCallback for Calender-TimeStamp mapping
class CalendarTypeHandlerCallback implements TypeHandlerCallback {

    private static final Calendar netWorkCal = Calendar.getInstance(TimeZone.getTimeZone("EST"));

 public Object getResult(ResultGetter getter) throws SQLException {
        Date date = getter.getDate(netWorkCal);
        Calendar calendar = null;

        if (date != null) {
            calendar = Calendar.getInstance();
            calendar.setTime(date);
        }

        return calendar;
    }

    public void setParameter(ParameterSetter setter, Object parameter)
        throws SQLException {
        GregorianCalendar calendar = (GregorianCalendar) parameter;
        java.sql.Date date = new java.sql.Date(calendar.getTimeInMillis());
        setter.setDate(date, netWorkCal);
    }

    public Object valueOf(String s) {
        return s;
    }
}
A Sample jdbc test case is uploaded here. If you are using Hibernate, You could use a user type to get similar effect.

Adventurously, I used the TimeZone.setDefault method on non-production systems of an application involving Tomcat, WebLogic a couple of wars and one ear. So far, I have not observed any issues, Google doesnot have any pages that give any known situations either. If I come to know, I will update.

Popular posts from this blog

One page Stock

Alright.. That was a long absence. The whole last week I dint blog. I dint go away. I was "occupied". I was learning stock trading. Its very fascinating. I have a good weeeked blog for you all. Here is my experience. I can literally hyper-link every word from the following paragraphs, but I am writing it as simple as I can so you can look up the italicised words in wikipedia . I got a paper trading account from a brokerage firm . You need one brokerage account first. Then it can be an Equity account where all your money is yours or a Margin account , where some of the money is lent by the brokerage firm. Then I get Buying power , which is the dollor value of how much stocks you can buy. I can make profit by simple rules. Buy when Price is low. Sell when price is high. There is another more intersting way of earning money. Selling short . Thats when price is not high, per say, but when are confident that the price WILL go down. then buy back when its lowest. This is what

Appcache manifest file issues/caveats

Application cache (appcache) is a powerful feature in HTML5. However, it does come with baggage. Many (see links below) advocated ferociously against it due to tricky issues it comes with. For someone who is just testing waters, these issues may throw them off grid. Knowing them before hand helps reduce some unpredictable effects.

classpath*: making your Modular Spring Resources

Spring gives multiple options to load XML resources for building contexts. the reference documentation does explain this feature quite well. However, I am taking my shot at explaining the different practical scenarios ( by order of growing modularisation) For Example, A simplest Spring based web Context Loader can be configured with resources like this <context-param> <param-name>contextConfigLocation</param-name> <param-value>applicationContext.xml</param-value> </context-param> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> You just need to put applicationContext.xml in WEB-INF/ folder of your webapp. However, Typically an application is n-tiered. You can also have multiple files setup and in relative paths. like <param-value> context-files/applicationContext.xml context-files/dao.xml context-files/service.xml </param-value>