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

Being a Vegetarian

I am a Proud Vegetarian. I don't eat Meat or Eggs. People say its hard here in US to be one. I beg to differ. The mere fact that I am hail and healthy these 4 years is a definitive proof. Apart from being bullied and trash talked by The Meat-Eaters, There is really nothing that makes this choice of mine any more than a debatable issue at a lunch or dinner. Other things aside, I am writing this blog having watched a PETA Video. Before you click on the play button, I ask you - If you are a vegetarian : Dont watch it. If you are not : Dare to watch it till the end. If you think going veg is just a fashion, think again . Even if you just want to do it for Fashion . Do it. Go Vegetarian. And Feel better asking the waiter for a Vegetarian Entrée in your next lunch.

Using Equinox CommandProvider to make OSGi console interactive.

After fiddling with the First Bundles that "Hello World"-ed upon Activation, You want to see more interactivity in OSGi. Although Using OSGi for an interactive Command Line Application would be like this one would be, well, a callable over-kill, I am going to start with an example and Expand it in later posts. So, please Welcome CommandProvider. CommandProvider is an EQUINOX specific API for extending the Console. This basic Example illustrates how to get a command from console and do something in java and also gets your feet wet on Service Registry package com.so.examples.commandconsole; import org. eclipse .osgi.framework.console .CommandInterpreter; import org.eclipse.osgi.framework.console.CommandProvider; public class DisplayMessageCommand implements CommandProvider { public void _say(CommandInterpreter ci) { ci.print("You said:" + ci.nextArgument()); } @Override public String getHelp() { return "\tsay - repeats what you say\n"; } }

How to Make a Local (Offline) Repository in Ubuntu / Debian

If you are in a place where you dont have internet (or have a bad one) You want to download .deb packages and install them offline. Each deb file is packaged as a seperate unit but may contain dependencies (recursively). apt-get automagically solves all the dependencies and installs all that are necessary. Manually install deb files one by one resolving each dependency would be tedious. A better approach is to make your own local repository. Before you actually make a repo, You need *all* deb files. You dont practically have to mirror all of the packages from the internet, but enough to resolve all dependencies. Also, You have to make sure, you are getting debs of the correct architecture of your system (i386 etc) # 1. make a dir accessible (atleast by root) sudo mkdir /var/my-local-repo # 2. copy all the deb files to this directory. # 3. make the directory as a sudo dpkg-scanpackages /var/my-local-repo /dev/null > \ /var/my-local-repo/Packages # 4. add the local repo to sour