Re: Timestamp without timezone issue

Chip Gobs <chip.gobs 'at' noaa.gov> writes:

> 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'

Rewriting the query for increased readability:

UPDATE arealobs
SET lid = 'NAME1', pe = 'PP', dur = 1001, ts = 'PM',
extremum = 'Z', obstime = '2007-11-30 10:00:00.000000-06', value = 0.0
WHERE lid = 'NAME1' AND pe = '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.

That is "arealobs_pk" I suppose?

My first question would be of logics: a primary key normally
designates a unique way of identifying an entry (a row) in a
table; therefore, it's normally not desirable to update the
primary key columns, when you are just updating the data relative
to a specific entry designed by the values of the primary key
columns (you're just updating the "value" column, if I guess
correctly). Your application would probably be more logical and
less bound to bugs if you just update the value column here?

> 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.

The fact that -06 is ignored when working with timestamp without
time zone seems normal; quoting the documentation: In a literal
that has been decided to be timestamp without time zone,
PostgreSQL will silently ignore any time zone indication. That
is, the resulting value is derived from the date/time fields in
the input value, and is not adjusted for time zone.

If you want the timezone to be used for properly offseting the
timestamp in input, you should use timestamp with time zone
datatype.

> 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);

Personally, I avoid using Timestamp.valueOf, because it uses the
JVM's timezone to compute the actual timestamp's value. This code:

System.out.println( "jvm's timezone: " + TimeZone.getDefault().getID() );
String input = "2007-12-05 10:00:00.000000000";
Timestamp ts = Timestamp.valueOf( input );
System.out.println( input + "'s is " + ts.getTime() + " milliseconds since January 1, 1970, 00:00:00 GMT" );

outputs that result:

- with the default timezone of my system:

jvm's timezone: Europe/Zurich
2007-12-05 10:00:00.000000000 is parsed to be 1196845200000 milliseconds since January 1, 1970, 00:00:00 GMT

- in UTC:

jvm's timezone: UTC
2007-12-05 10:00:00.000000000 is parsed to be 1196848800000 milliseconds since January 1, 1970, 00:00:00 GMT

Instead, I always parse a date-time input using date formatters
(with date formatters at the configured time zone of the
application, actually).

I think your workaround may work because the timezone of your
system is -06. The actual timestamp object is shifted because of
Timestamp.valueOf's behaviour. If this is what you want, you
should rather use a date formatter at the desired time zone.

For the record: we always use timestamp with time zone in our
database, to avoid time zone manipulation problems and
confidently be able to change the used timezone in the
application (or for users), whatever timezone is used internally
by the database and/or by the system.

--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland