Julian Hyde created CALCITE-5446:
------------------------------------
Summary: Support TIMESTAMP WITH LOCAL TIME ZONE type in JDBC driver
Key: CALCITE-5446
URL: https://issues.apache.org/jira/browse/CALCITE-5446
Project: Calcite
Issue Type: Bug
Reporter: Julian Hyde
Support TIMESTAMP WITH LOCAL TIME ZONE type in JDBC driver.
Currently, Calcite types with {{SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE}}
are mapped to JDBC type 1111 (OTHER). As a result, the ResultSet.toString()
method just converts the (signed) {{long}} value to a string.
Here is what
[Progress|https://docs.progress.com/en-US/bundle/datadirect-connect-jdbc-51/page/TIMESTAMP-WITH-LOCAL-TIME-ZONE-Data-Type.html]
says:
{quote}
The Oracle {{TIMESTAMP WITH LOCAL TIME ZONE}} data type is mapped to the JDBC
{{TIMESTAMP}} data type.
When retrieving {{TIMESTAMP WITH LOCAL TIME ZONE}} columns, the value returned
to the user is converted to the time zone specified by the {{TIME_ZONE}}
session parameter.
When setting {{TIMESTAMP WITH LOCAL TIME ZONE}} columns:
* Using a timestamp (using {{PreparedStatement.setTimestamp}}, for example),
the value set is converted to the time zone specified by the {{TIME_ZONE}}
session parameter.
* Using a string (using {{PreparedStatement.setString()}}, for example), the
string is passed as-is to the server. The supplied string must be in the format
specified by the {{NLS_TIMESTAMP_TZ_FORMAT}} session parameter. If not, the
Oracle server generates an error when it attempts to convert the string to the
{{TIMESTAMP WITH LOCAL TIME ZONE}} type.
{quote}
We should definitely map to {{java.sql.Types.TIMESTAMP}}.
I'm not sure whether Calcite has a {{TIME_ZONE}} session parameter. But values
should be shifted from/to the desired time zone on the way in/out.
And maybe there should also be a Calcite server time zone that is the time zone
in which {{TIMESTAMP WITH LOCAL TIME ZONE}} values are represented. By default
this should be UTC, and using a time zone with a variable offset from UTC (e.g.
"Pacific/Los_Angeles") would be a bad idea.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
