You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Gregory Hart (Jira)" <ji...@apache.org> on 2022/12/06 00:50:00 UTC
[jira] [Commented] (CALCITE-2989) Use ISO-8601 calendar when converting between java.sql types and unix timestamps
[ https://issues.apache.org/jira/browse/CALCITE-2989?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17643612#comment-17643612 ]
Gregory Hart commented on CALCITE-2989:
---------------------------------------
[~julianhyde], [~rubenql], and [~asolimando], thanks for your feedback! I've incorporated your suggestions into the merge request. I've run the tests in multiple timezones: UTC, America/Los_Angeles, America/New_York, Pacific/Chatham, Pacific/Samoa, and Pacific/Tongatapu. It's ready for your final review and approval.
I've also included one semi-related change which is to modify the getString() methods of AbstractCursor.DateAccessor, TimeAccessor, and TimestampAccessor to use the time zone of the localCalendar variable. This matches the behavior of DateFromNumberAccessor, TimeFromNumberAccessor, and TimestampFromNumberAccessor. It's needed for a test in the related CALCITE-5414 ticket where I verify that ResultSet.getString(int) returns the same value as ResultSet.getDate(int) or ResultSet.getTimestamp(int) for certain dates or timestamps.
> 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
> Time Spent: 50m
> 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)