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

Re: [BUGS] BUG #1523: precision column value returned from

From: Sergio Lob <Sergio_Lob(at)iwaysoftware(dot)com>
To: oliver(at)opencloud(dot)com
Cc: pgsql-jdbc(at)postgresql(dot)org, Ephraim_Spravtsev(at)iwaysoftware(dot)com,"Lob, Sergio" <Sergio_Lob(at)iwaysoftware(dot)com>
Subject: Re: [BUGS] BUG #1523: precision column value returned from
Date: 2005-03-11 14:56:35
Message-ID: 4231B1A3.4050304@iwaysoftware.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-jdbc
I'll quote you what Oracle docs for their support of TIMESTAMP w/ time 
zone....

"TIMESTAMP WITH TIME ZONE Data Type

By default, the Oracle TIMESTAMP WITH TIME ZONE data type is mapped to 
the VARCHAR JDBC data type.

When retrieving TIMESTAMP WITH TIME ZONE values as a string (using 
resultSet.getString, for example), the value is returned as the string 
representation of the timestamp including time zone information. The 
string representation is formatted in the format specified by the Oracle 
NLS_TIMESTAMP_TZ_FORMAT session parameter.

By default, retrieving TIMESTAMP WITH TIME ZONE values as a timestamp 
(using resultSet.getTimeStamp, for example) is not supported because the 
time zone information stored in the database would be lost when the data 
is converted to a timestamp. To provide backward compatibility with 
existing applications, you can use the FetchTSWTZasTimestamp property to 
allow TIMESTAMP WITH TIME ZONE values to be retrieved as a timestamp. 
The default value of the FetchTSWTSasTimestamp property is false, which 
disables retrieving TIMESTAMP WITH TIME ZONE values as timestamps."

Oracle recognizes that the JDBC timestamp data type does not allow for 
timezones, thus they would map PostgresSQL timestamptz type to JDBC type 
varchar, not timestamp. PostgreSQL currently maps timestamptz to JDBC 
timestamp in DatabaseMetadata.getTypeInfo() method and in 
ResultSetMetaData.getColumnType() method. I tend to agree with their 
interpretation.

Regards, Sergio


oliver(at)opencloud(dot)com wrote:

>Sergio Lob wrote:
>
>  
>
>>What format does PostgreSQL return in getString() method for a timestamp column, for example? Seems like it should return same as toString() method of a timestamp object as defined in java 2 api spec.
>>    
>>
>
>It returns the string representation the backend gave it. This is true 
>for all types at the moment. For a timestamp with timezone value this is 
>generally going to be yyyy-mm-dd hh:mm:ss.nnnnnnzzz:
>
>test=# select now();
>               now
>-------------------------------
>  2005-03-10 10:08:11.707753+13
>(1 row)
>
>Does the JDBC spec say somewhere that we should return a different format?
>
>-O
>  
>

In response to

Responses

pgsql-bugs by date

Next:From: Sergio LobDate: 2005-03-11 15:55:35
Subject: Re: [BUGS] BUG #1523: precision column value returned from
Previous:From: Terry YaptDate: 2005-03-11 13:37:37
Subject: Re: We are not following the spec for HAVING without GROUP

pgsql-jdbc by date

Next:From: Sergio LobDate: 2005-03-11 15:55:35
Subject: Re: [BUGS] BUG #1523: precision column value returned from
Previous:From: patrickDate: 2005-03-11 14:15:49
Subject: Cannot Retrieve Binary Data

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