[JDBC] Timestamp without timezone issue

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:

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related postgresql.org Links pgsql-admin pgsql-advocacy pgsql-announce pgsql-bugs pgsql-cygwin pgsql-docs pgsql-general pgsql-interfaces pgsql-jdbc pgsql-jobs pgsql-novice pgsql-odbc pgsql-performance pgsql-php pgsql-ports pgsql-sql Request more information about Pantek

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

---------------------------(end of broadcast)---------------------------

                
http://www.postgresql.org/about/donate