We recently changed from using PostgreSQL 7.4.8 to 8.2.5 and switched
the JDBC driver to the 8.2.506 version from the 74.215 version. We are
and have been using build 1.5.0_04-b05 of the J2SE since before our
Postgres version change.
After switching, we started receiving large numbers of errors in the
postgres error log file. These are unique constraint errors on UPDATEs,
when we are not actually trying to change any of the key columns. The
errors are reported as follows (irrelevant non-key columns have been
removed for clarity):
Nov 30 13:25:12 machinename postgres[29003]: [13-1] ERROR: duplicate
key violates unique constraint "arealobs_pk"
Nov 30 13:25:12 machinename postgres[29003]: [13-2] STATEMENT: UPDATE
arealobs SET lid = 'NAME1', pe = 'PP', dur = 1001, ts = 'PM', extremum =
'Z', obstime = '2007-11-30
Nov 30 13:25:12 machinename postgres[29003]: [13-3]
10:00:00.000000-06', value = 0.0, Nov 30 13:25:12 machinename
postgres[29003]: [13-4] WHERE lid = 'NAME1' AND pe
Nov 30 13:25:12 machinename postgres[29003]: [13-5] = 'PP' AND dur =
'1001' AND ts = 'PM' AND extremum = 'Z' AND obstime = '2007-11-30 16:00:00'
The key columns on this table are lid, pe, dur, ts, extremum and obstime.
Notice the (-06 US Central time) time zone information in the log
message.
The column obstime is of type timestamp without timezone. After using
psql to experiment, it appears that the -06 is being ignored and the
time in the value assignment part of the update statement is being
considered as 10:00:00 UTC instead of 16:00:00 UTC.
A workaround is to use:
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
formatter.setTimeZone(TimeZone.getTimeZone("UTC"));
dateTimeString = formatter.format(new java.util.Date(timeInMillis));
Timestamp timestamp = Timestamp.valueOf(dateTimeString);
statement.setTimestamp(index, timestamp);
The following did not work:
TimeZone tz = TimeZone.getTimeZone("UTC");
Calendar cal = Calendar.getInstance(tz);
Timestamp timestamp = new Timestamp(timeInMillis);
statement.setTimestamp(index, timestamp , cal);
Neither did:
Timestamp timestamp = new Timestamp(timeInMillis);
statement.setTimestamp(index, timestamp);
Is this a known issue, a new one, or was I doing something wrong?
Thanks,
Chip Gobs
