Re: Timestamp without timezone issue - Mailing list pgsql-jdbc

Let me be more clear about the update query  that I quoted.  It was
created using the JDBC driver, which was adding the time zone
information.  I don't want any timezone information in my query.  We are
using the <timestamp without timezone> type and the time always
represents UTC.

About updating key columns,  I agree that it is neither necessary nor
desirable to update the key columns.
We have a code generator to create nearly all of our JDBC code and
already have plans to change it to update only non-key columns.  That
said, bugs are rare using the generated code.
But changing that won't solve my problem of having <timestamp without
timezone> column values altered by the driver, whose behavior seems to
have changed with the newer version.   I have 2 non-key timestamp
columns (that I omitted for brevity) that need to be updated with the
correct time.

So my question is, other than my workaround:

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

How do I tell the driver to leave my <timestamp without timezone> values without a timezone attached to them?


Why does

Timestamp timestamp = new Timestamp(timeInMillis);
statement.setTimestamp(index, timestamp);

not work?  The timeInMillis value is supposed to represent millis since
Jan 1, 1970 00:00:00 GMT.


Thanks,

Chip


Guillaume Cottenceau wrote:
> 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.
>
>