Posted on Friday, July 01, 2005 10:30 PM
I am using Ibatis and PostgreSQL for this great project, www.bestlaps.com. However, we've recently ran into a strange problem where we store a java.util.Date into a postgress TIMEZONE column, which works fine. However, when you want to retreive it, Ibatis returns a "StringIndexOutofBoundsException" at position 23. For some reason, the result returned by Postgres can not be converted back into a java.util.Date.
I've tried really hard to circumvent this problem with custom setter methods (parsing the results myself), and using jodatime (a great java date and time replacement library). The fix is as simple as it is strange: Make Ibatis convert the timestamp into a java.sql.Date, without changing your code.
So: You have your normal bean, in which you use java.util.Date. Then, make your Ibatis sqlMap as usual, but for the timestamp column you put the following in the resultmap: javaType="java.sql.Date".
Strange, but it works for now. Maybe more info on this later.
UPDATE
Okay, after spending the whole weekend with this problem I discovered that the trick I just described does noet work. java.sql.Date has no time component, so when you retreive it, your java.util.Date in the bean will have a 00:00 time. So I had to search a bit further.
When querying with SquirrelSQL, I can insert and retreive a timestamp to and from Postgress without it being changed. So it seems that it is no Postgress bug. When I am using Ibatis to insert a java.util.Date into Postgress, it gets "timezone corrected", although I specifically told Postgress to store timestamps without timezones.
The time gets corrected by 2 hours. This seems to come from the util.Date class, which seems to have a default offset of -120 minutes, although I did not set that, and did not request any timezone to be set.
I tried to make my own typeHandler for Ibatis, where I could convert the java.util.Date to a java.sql.TimeStamp, and give it directly to the PreparedStatement. But even that did not help. I am guessing the bug is in the Postgress JDBC driver, although that conflicts with the fact that SquirelSQL (which is als a Java/JDBC program) can do it.
THE FIX
After more than 16 hours (!) of research I decided I would go for the workaround. We are behind schedule allready for the www.bestlaps.com project, so I needed a quick fix. The way to go was dissapointingly simple: Convert Date's to Longs (with getTime()) and store the Longs in the database as number. This way ibatis and postgress don't know that it's a time. Pay attention to the fact that you need a postgress BIGINT to store the large number of milliseconds since the epoch.
Final thoughts
It seems that people who do not know how to handle timezones have built code to handle timezones. For Java, Postgres, Ibatis, or somewhere around that neighbourhood. Oh well, we knew that Java doesn't have a great Date system. A promising solution I found (but not used yet) is JodaTime.
I really hope the bug gets fixed soon so I can have proper dates in the database when we go live. For now, Longs work (and will probably stay in to the end of time).