Re: [BUGS] BUG #1523: precision column value returned from - Mailing list pgsql-jdbc

By the way, the PostgreSQL ODBC driver has different format for
retrieved timestamps w/ time zone.

For instance, an inputted timestamp w/ time zone value of  '1999-10-30
11:12:13-8', which is pacific standard time zone, displayed a retrieved
character string value of '1999-10-20 15:12:13'  (without the time zone
designation), I suppose it must translate it to the current locale's
timezone. This output format here would conform to the java 2 api spec
JDBC timestamp escape format defined in timestamp.toString() method
description. But I still like Oracle's definition best...

Sergio

Sergio_Lob@iwaysoftware.com wrote:

>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 (u1999-10-30 15:12:13sing resultSet.getString, for
example),the value is returned as the string representation of the timestamp including time zone information. The
stringrepresentation 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
notsupported 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
toallow TIMESTAMP WITH TIME ZONE values to be retrieved as a timestamp. The default value of the FetchTSWTSasTimestamp
propertyis 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
timestamptztype 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@opencloud.com wrote:
>
>
>Sergio Lob wrote:
>
>
>
>
>
>What format does PostgreSQL return in getString() method for a timestamp column, for example? Seems like it should
returnsame 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
>
>
>
>
>