[jira] [Comment Edited] (CALCITE-5446) Support TIMESTAMP WITH LOCAL TIME ZONE type in JDBC driver

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5446?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17649520#comment-17649520 ] 

Julian Hyde edited comment on CALCITE-5446 at 12/20/22 4:39 PM:
----------------------------------------------------------------

I suspect that TIMESTAMP WITH LOCAL TIME ZONE values should end with the time zone (often UTC) when converted to strings. And conversely when supplied via PreparedStatement.setString, a time zone is optional. 

I have started work on this in my dev branch for CALCITE-5424; I won’t finish the job but someone can use my work as a starting point. 




was (Author: julianhyde):
I have started work on this in my dev branch for CALCITE-5424.

> 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
>            Priority: Major
>
> 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)