[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16912895#comment-16912895
]
Kenneth Knowles commented on CALCITE-2394:
--
FWIW here I've recently realized that Beam SQL is probably backwards. We have
been mapping an absolute Joda-style instant to the Calcite type TIMESTAMP which
is pretty explicitly wrong. We probably need to decide between `TIMESTAMP WITH
TIMEZONE` to make it an absolute time (with extraneous metadata) versus
`TIMESTAMP WITH LOCAL TIMEZONE` which to be honest I don't really understand.
> 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
(v8.3.2#803003)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16913570#comment-16913570
]
Julian Hyde commented on CALCITE-2394:
--
[~kenn] I feel your pain. There is a parallel universe where JDBC was written
after after JodaTime was introduced, and {{ResultSet.getTimestamp}} returned a
[LocalDateTime|http://joda-time.sourceforge.net/apidocs/org/joda/time/LocalDateTime.html]
(which exactly matches the semantics of a SQL TIMESTAMP), and none of this
pain with calendars and timezone offsets would ever have happened. Sadly we do
not live in that universe.
> 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
(v8.3.2#803003)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17345559#comment-17345559
]
Kenneth Knowles commented on CALCITE-2394:
--
Unassigning to be realistic about the fact that I am not working on this and
will not have time to do so.
> 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
>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
(v8.3.4#803005)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17627304#comment-17627304
]
Ian Bertolacci commented on CALCITE-2394:
-
Is this related to, or the cause of, CALCITE-4120?
> 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
>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
(v8.20.10#820010)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530178#comment-16530178
]
ASF GitHub Bot commented on CALCITE-2394:
-
GitHub user kennknowles opened a pull request:
https://github.com/apache/calcite-avatica/pull/63
[CALCITE-2394] Fix TIMESTAMP accessors: test with non-default, non-zero
timezone
This removes some millis-since-epoch arithmetic from the accessor for a
timestamp. In fact, no arithmetic should be necessary for absolute instant
references on the data plane, but only during pretty printing. The issue
manifests as timestamp columns always being off for any non-zero timezone
locale.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/kennknowles/calcite-avatica timestamp
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/calcite-avatica/pull/63.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #63
commit 9f6e246e3b9d952341eff37650fa866c31952292
Author: Kenneth Knowles
Date: 2018-07-02T16:10:43Z
Fix TIMESTAMP accessors: test with non-default, non-zero timezone
> 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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530272#comment-16530272
]
ASF GitHub Bot commented on CALCITE-2394:
-
Github user julianhyde commented on the issue:
https://github.com/apache/calcite-avatica/pull/63
Please see discussion in https://issues.apache.org/jira/browse/CALCITE-2394.
> 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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530293#comment-16530293
]
Kenneth Knowles commented on CALCITE-2394:
--
I think there is a step prior to SQL or JDBC. Ignoring relativity, a moment in
time has no time zone, platonically.
What I'd like to know, then, is how to correctly store such a thing
(millisecond precision suffices for now) and retrieve it using Calcite Avatica.
> 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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530319#comment-16530319
]
Kenneth Knowles commented on CALCITE-2394:
--
Not to say that such a thing is without a _shared point of reference_, which is
essentially universally 1970-01-01 00:00:00Z. But, critically, it is _not_
parameterized by any additional data such as a timezone provided at retrieval
time.
> 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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530345#comment-16530345
]
Julian Hyde commented on CALCITE-2394:
--
Well, there's one way to find out what the SQL standard actually says. :)
bq. I think there is a step prior to SQL or JDBC. Ignoring relativity, a moment
in time has no time zone, platonically.
Yes, you're talking about what Joda Time (and java.time) calls an Instant.
Sadly standard SQL does not have such a concept. (I believe that SQL standard
TIMESTAMP corresponds to a Joda LocalDateTime, and TIMESTAMP WITH TIMEZONE to
DateTime.) We introduced TIMESTAMP WITH LOCAL TIME ZONE to try to create it.
bq. What I'd like to know, then, is how to correctly store such a thing
(millisecond precision suffices for now) and retrieve it using Calcite Avatica.
Have you tried passing a Calendar to the ResultSet.getTimestamp and
PreparedStatement.setTimestamp methods whose time zone is UTC?
By the way, I would love to add support to Avatica so that people can go
directly to java.time types.
> 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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530350#comment-16530350
]
Julian Hyde commented on CALCITE-2394:
--
By the way. We have not considered how the TIMESTAMP WITH LOCAL TIME ZONE type
should be handled in Avatica, but clearly the right thing is not perform
timezone translation. For instance, you should be able to call
PreparedStatement.setObject with an Instant, then later call
ResultSet.getObject(int, Instant.class).
> 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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530361#comment-16530361
]
Kenneth Knowles commented on CALCITE-2394:
--
Another clarification: my system is using SqlLine + Avatica-based Beam SQL JDBC
driver so I don't directly control any code calling either method (or at least
the value proposition of using this toolchain is that I don't have to). What
you've said makes it sound like it is the responsibility of the JDBC driver to
choose a timezone for TIMESTAMP values. I would very much like this arbitrary
choice to always be UTC aka zero offset. Perhaps this is a configuration option
my entry point to Avatica can set up?
> 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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530367#comment-16530367
]
ASF GitHub Bot commented on CALCITE-2394:
-
Github user kennknowles commented on the issue:
https://github.com/apache/calcite-avatica/pull/63
Got it. Closing this for bit.
> 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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530368#comment-16530368
]
ASF GitHub Bot commented on CALCITE-2394:
-
Github user kennknowles closed the pull request at:
https://github.com/apache/calcite-avatica/pull/63
> 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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530382#comment-16530382
]
Julian Hyde commented on CALCITE-2394:
--
Per
[Rows.java|https://github.com/julianhyde/sqlline/blob/b14152adc0c8df2554334df57724b034fc5f77b1/src/main/java/sqlline/Rows.java#L194],
SQLline seems to be calling ResultSet.getString() if it is a TIMESTAMP value.
That seems to be the right thing to do. As long as a the driver isn't trying to
convert the SQL TIMESTAMP into a java.sql.Timestamp and/or apply timezone
translation before converting to string.
> 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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530409#comment-16530409
]
Kenneth Knowles commented on CALCITE-2394:
--
I believe the calls go like so:
1. ResultSet.getString(col)
2. AvaticaResultSet.getString(col))
3. AvaticaResultSet.getAccessor(col).getString()
4. TimestampAccessor.getString()
5. timestampAsString(TimestampAccessor.getNumber())
6. DateTimeUtils.unixTimestampToString(getNumber() - offset)
7. DateTimeUtils.unixTimestampToString(AvaticaSite.toBigDecimal(col) - offset)
I would describe this as applying timezone translation before converting to
string. It does not convert to java.sql.Timestamp.
To set up a fuller example for consideration, which I imagine is fairly
pedestrian: I have a CSV in text or Kafka (etc) with unix timestamps. A user
imposes a schema on it (CREATE TABLE ... LOCATION ...). They claim that column
has type TIMESTAMP. Can I make this always have zero offset? My reading of your
advice is that the user must impose the type TIMESTAMP WITH TIME ZONE and we
always return a timezone with offset 0. TBH this path has not been on my radar
so I don't know if it turns out well. It would be much nicer for users if they
didn't have to type so much.
> 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)
[jira] [Commented] (CALCITE-2394) Avatica applies calendar offset to timestamps when they should remain unchanged
[
https://issues.apache.org/jira/browse/CALCITE-2394?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16539259#comment-16539259
]
Andrew Pilloud commented on CALCITE-2394:
-
Looks like there is a config option in ConnectionProperty for timeZone and Beam
should be setting that to UTC, which will result in Avatica applying the local
timezone offset of 0.
> 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)
