[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530253#comment-16530253
]
Julian Hyde commented on CALCITE-2394:
--------------------------------------
There's a common misunderstanding about how SQL and JDBC handle time zones. I
think this is the cause of your problem.
In SQL, a timestamp has no time zone. Not the system time zone, not UTC, no
time zone. The interpretation is up to the person who receives the result of
the query.
In Java, a timestamp always has time zone UTC (when you call toString(), it is
printed in local timezone, but let's ignore that). Its internal value is
milliseconds since UTC epoch.
Since JDBC converts from the SQL semantics to Java semantics, the
ResultSet.getTimestamp has to convert a zoneless timestamp has to be converted
to a zoned timestamp. The calendar argument allows the user to specify what
timezone the value was stored in. Then JDBC converts the value to UTC -
remember, java timestamps are always UTC internally - by subtracting the
timezone offset.
Maybe your system's semantics are that timestamps have a particular time zone.
If so, your values are not SQL {{TIMESTAMP}} values; they are more like
{{TIMESTAMP WITH LOCAL TIME ZONE}}, see CALCITE-1947. People should call
{{ResultSet.getTimestamp(int)}} on such types, rather than
{{ResultSet.getTimestamp(int, Calendar)}}.
> Avatica applies calendar offset to timestamps when they should remain
> unchanged
> -------------------------------------------------------------------------------
>
> Key: CALCITE-2394
> URL: https://issues.apache.org/jira/browse/CALCITE-2394
> Project: Calcite
> Issue Type: Bug
> Components: avatica
> Reporter: Kenneth Knowles
> Assignee: Kenneth Knowles
> Priority: Major
>
> This code converts a millis-since-epoch value to a timestamp in three
> different accessors:
> {code}
> class AbstractCursor {
> ...
> static Timestamp longToTimestamp(long v, Calendar calendar) {
> if (calendar != null) {
> v -= calendar.getTimeZone().getOffset(v);
> }
> return new Timestamp(v);
> }
> }
> {code}
> But {{new Timestamp(millis)}} always accepts millis-since-epoch in GMT.
> The use in {{DateFromNumberAccessor}} is probably OK: it fabricates
> millis-since-epoch from a date, so applying the offset is appropriate to hit
> midnight in that locale.
> But both {{TimeFromNumberAccessor}} and {{TimestampFromNumberAccessor}}
> should leave the millis absolute.
> This manifests as timestamp actual values being shifted by the current locale
> (in addition to later display adjustments).
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
