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

Re: Timestamp Conversion Woes Redux

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Oliver Jowett <oliver(at)opencloud(dot)com>,Kris Jurka <books(at)ejurka(dot)com>,Christian Cryder <c(dot)s(dot)cryder(at)gmail(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Timestamp Conversion Woes Redux
Date: 2005-07-22 12:58:51
Message-ID: 9F702079-9C1A-4606-903A-040F8F4C67FD@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
On 21-Jul-05, at 8:54 PM, Tom Lane wrote:

> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
>
>> i.e. currently the core of the problem is this behaviour:
>>
>
>
>> template1=# select '2005-01-01 15:00:00  
>> +1000'::timestamptz::timestamp;
>>       timestamp
>> ---------------------
>>  2005-01-01 18:00:00
>> (1 row)
>>
>
> Well, the current interpretation is that timestamptz -> timestamp
> produces a timestamp representing what the timestamptz equates to in
> your current TimeZone.  I do not foresee changing that behavior  
> when/if
> we add explicit TZ info to timestamptz --- it would break just about
> every existing app that uses this conversion.
>
> In any case, this wouldn't solve Christian's complaint, because the  
> root
> of his problem is that the value ever goes through timestamptz at all.
> That is necessarily going to "munge" values that don't correspond to
> legal local times in whatever zone you are using.
>
> The more I think about this, the more I think that the correct  
> solution
> must include having the driver set TimeZone = UTC (and complain if the
> app tries to change it).

This really isn't an option. We can't impose limits on the  
application like this.

> Then you can specify parameter types as either
> timestamp or timestamptz, it doesn't really matter, because  
> conversions
> between them on the server side will be no-ops.  When you convert a  
> Java
> Timestamp to send to the server, you always convert it using a UTC
> Calendar object.  I'm not sure if the setTimestamp variants with a
> Calendar are special in this regime; arguably you should ignore the
> supplied Calendar, on the principle that you know what the  
> Timestamp is
> supposed to mean.
>
>             regards, tom lane
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>


In response to

Responses

pgsql-jdbc by date

Next:From: Tom LaneDate: 2005-07-22 13:42:34
Subject: Re: Timestamp Conversion Woes Redux
Previous:From: Dave CramerDate: 2005-07-22 12:45:42
Subject: Re: Timestamp Conversion Woes Redux

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