[
https://issues.apache.org/jira/browse/CALCITE-5446?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17688627#comment-17688627
]
Will Noble commented on CALCITE-5446:
-------------------------------------
Adding my thoughts on the proper semantics for these timestamps types from
CALCITE-5500 which was marked as a duplicate of this:
{{TIMESTAMP}}:
* {{getString()}} -> Always returns the same string no matter the client's time
zone
* {{getTimestamp()}} -> Subtracts the zone offset from the instant, so
rendering the instant to a string in the client's time zone produces the same
result as {{getString()}}
{{TIMESTAMP WITH LOCAL TIME ZONE}}:
* {{getTimestamp()}} -> Always returns the same instant no matter the time zone
* {{getString()}} -> Adds the zone offset to the instant before rending it to a
string, so rendering the result of {{getTimestamp()}} in the client's time zone
produces the same result as this.
> 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
> Components: avatica
> Reporter: Julian Hyde
> Assignee: Oliver Lee
> Priority: Major
> Labels: pull-request-available
> Time Spent: 3h 10m
> Remaining Estimate: 0h
>
> 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)
