[
https://issues.apache.org/jira/browse/CALCITE-2989?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17694192#comment-17694192
]
John Wright commented on CALCITE-2989:
--------------------------------------
[~freastro] / [~julianhyde] , after uptaking this change we're seeing incorrect
timestamp values. It appears to be this line that is injecting the machine's
default timezone (in our case, PST) instead of standardizing on UTC:
[https://github.com/apache/calcite-avatica/commit/dbe9b1d8c2e53474eb40cfaf5721aceca3bdb57f#diff-1c2eb1955a12853964307d890ffd08d66512822012de17c23437faa5a19f9c40R1398]
Previously:
{code:java}
v -= calendar.getTimeZone().getOffset(v);{code}
After the change:
{code:java}
sqlTimestamp.setTime(time + DEFAULT_ZONE.getOffset(time) - offset); {code}
It is quite problematic for us to ensure DEFAULT_ZONE is UTC on every machine.
Even though we're passing in a UTC calendar and the long value is getting
converted into PST. Is there something I'm missing with this code?
> Use ISO-8601 calendar when converting between java.sql types and UNIX
> timestamps
> --------------------------------------------------------------------------------
>
> Key: CALCITE-2989
> URL: https://issues.apache.org/jira/browse/CALCITE-2989
> Project: Calcite
> Issue Type: Bug
> Components: avatica
> Reporter: vinoyang
> Assignee: Gregory Hart
> Priority: Major
> Labels: pull-request-available
> Fix For: avatica-1.23.0
>
> Time Spent: 2h 10m
> Remaining Estimate: 0h
>
> Converting java.sql types to unix timestamps requires extra steps to also
> convert to the correct calendar. Unix timestamps should follow the proleptic
> Gregorian calendar as defined by ISO-8601. Java uses the standard Gregorian
> calendar for java.sql types and switches to the Julian calendar for dates
> before the Gregorian shift.
> If we uses avatica's {{DateTimeUtils}} the dates less than 2299161 will cause
> an error result in Flink table/sql , test code :
> {code:java}
> testAllApis(
> "1500-04-30 12:00:00".cast(Types.SQL_TIMESTAMP),
> "'1500-04-30 12:00:00'.cast(SQL_TIMESTAMP)",
> "CAST('1500-04-30 12:00:00' AS TIMESTAMP)",
> "1500-04-30 12:00:00.0")
> {code}
> result :
> {code:java}
> Expected :1500-04-30 12:00:00.0
> Actual :1500-04-20 12:00:00.0
> {code}
> another case is here :
> https://issues.apache.org/jira/browse/FLINK-11935
> I find a key code snippet has been removed in CALCITE-1884 which caused this
> issue :
> {code:java}
> if (j < 2299161) {
> j = day + (153 * m + 2) / 5 + 365 * y + y / 4 - 32083;
> }
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
