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

Javascript: Convert Strings to Binary (and representing in a nerdy way!)

I follow those GoogleDevelopers Videos . Sometime back, in one of the presentations on GoogleIO, there was this interesting string of dots at the bottom of each page of the presentation . They looked like random big and small dots. A similar bunch of dots were also on the T-shirt of a presenter was wearing in another presentation . While it seemed something in the pattern, I could not find what it was. Finally, another presenter cleared the matter that those dots are just binary representation of "GOOGLEIO" (So much for advertizing Google IO, Impressive!). So I wanna do it. Takes me back to days of those DSP classes at school. Nerdy me had to churn some old brain cells. I remember those first programming language classes in Pascal and C when you were asked to do fibonacci series and converting a binary string to ascii codes. That *experience* came handy here: Check it out! Text to Binarize: For those who came to copy the javascript code to convert string to binary,

MySql Copying Table Structures.

Some times you need to copy only table structures across databases. This article describes two ways of doing it. If the whole database schema need to be exported, mysqldump is very effective. A --nodata flag will dump all tables' schema. Like this. mysqldump --nodata -p -u username databaseName But if you want to copy a specific table, individually, you could use "create table like" feature. You could create it even from a different database. However it must be on the same mysqld instance. Like this. create table newtable like oldtable; --Or from a table in other database create table mytable like otherdatabase.tablename;

javascript maxlength for textarea with \r\n breaks in java (esp Firefox)

Textareas allow new lines to enter. These are represented by \n (1) or \r\n (2) characters. But when you save to DB you have a limit to certain length of chars. There is no maxlength attribute in HTML that will stop you from entering data. This is generally acomplished by Javascript. You do a onkeyup hook and stop event or trim after textarea.value.length > maxlength. There are many other solutions out there.. But.. Here is the problem that most of those solutions overlook, How do you deal with the count on \n and \r\n representations. Lets first see how it matters. If the text entered has new lines, the length is calculated differently in Firefox and IE. When you enter a Text like 01234 567890 You expect the textarea.value.length to be 11. (10 chars + new line).On the backend, however, java would recieve it as 12 chars (10 chars + \r\n) (this is irrespective of FF or IE). So you are effectively saving 12 chars to DB. Worse yet, IE seems to figure textarea.value.length as 12 (