Timestamp without timezone issue
От | Chip Gobs |
---|---|
Тема | Timestamp without timezone issue |
Дата | |
Msg-id | 4755B864.4000203@noaa.gov обсуждение исходный текст |
Ответы |
Re: Timestamp without timezone issue
Re: Timestamp without timezone issue |
Список | pgsql-jdbc |
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
В списке pgsql-jdbc по дате отправления: