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@opencloud.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
