problems with time zones in JDBC

The JDBC driver included with the current 6.5 betas appears to have the
same problems with time zones in getTimestamp() that 6.4.2 had. This
has caused problems with my JDBC application.

Discussion and suggested fixes are below.

The relevant code is in getTimestamp(int) in ResultSet.java:

1 public Timestamp getTimestamp(int columnIndex) throws SQLException
2 {
3 String s = getString(columnIndex);
4 SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd
HH:mm:sszzz");
5 if (s != null)
6 {
7 int TZ = new Float(s.substring(19)).intValue();
8 TZ = TZ * 60 * 60 * 1000;
9 TimeZone zone = TimeZone.getDefault();
10 zone.setRawOffset(TZ);
11 String nm = zone.getID();
12 s = s.substring(0,18) + nm;

There are two problems, as I see it.

1) The last line should be:
s = s.substring(0,19) + nm;

In Java, the second argument to substring() is 1 more than the last
index to include in the substring. Using 18 will drop the last digit of
the seconds field of the time. Not only does this lose data, but some
versions of Java will fail to parse the resulting malformed time string.

2) I believe that everything from line 7 to 12 (inclusive) should be
deleted. It appears that what is going on here is that the time zone,
expressed as an offset in hours from GMT, is extracted from the supplied
timestamp (line 7), and used to set the raw offset of the TimeZone
object "zone" (line 10). Then, on line 11, the ID field from the zone
is extracted, and appended to the supplied string on line 12.

The problem is that setting the raw offset of a TimeZone object does not
change the ID field. Thus, what this code does is appends the current
local time zone to all supplied timestamp strings, which is clearly not
correct.

The Java time/date parser can directly handle time/date strings that
express the timezone in hours offset from GMT, which is the format that
is being generated by PostgreSQL in the first place. Thus, deleting
lines 7 through 12 will restore correct timezone handling.

Of course, implementing fix #2 eliminates the need for fix #1.

I made these changes on my copy, and found that they worked, so timezone
handling worked as it ought to. (My application is used by users in a
variety of time zones, and records the time&date of various events, so
it is essential that correct time zone info be preserved.)

I'll submit this as a patch if needed, but I wanted to make sure that
I'm not missing something important before doing so.

Andrew Merrill
andrew(at)compclass(dot)com