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

On 5-Dec-07, at 8:13 AM, Chip Gobs wrote:

> 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.
>
Unfortunately the JDBC spec does not understand timestamp without
timezone. A timestamp object is extended from Date which has time zone
information.
> 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.
>
>
Timestamp has timezone information in it...
> 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.
>>
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>               http://www.postgresql.org/about/donate