Skip site navigation (1) Skip section navigation (2)

Re: Timestamp without timezone issue

From: Chip Gobs <chip(dot)gobs(at)noaa(dot)gov>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: Guillaume Cottenceau <gc(at)mnc(dot)ch>
Subject: Re: Timestamp without timezone issue
Date: 2007-12-05 13:13:32
Message-ID: 4756A3FC.3030807@noaa.gov (view raw or flat)
Thread:
Lists: 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.
>
>   


In response to

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2007-12-05 13:59:00
Subject: Re: Timestamp without timezone issue
Previous:From: Guillaume CottenceauDate: 2007-12-05 11:04:34
Subject: Re: Timestamp without timezone issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group