[CALCITE-2989] Use ISO-8601 calendar when converting between java.sql types and UNIX timestamps

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: avatica-1.23.0
• Component/s: avatica
• Labels: pull-request-available
• Assignee: Gregory Hart
• Reporter: vinoyang
• Created: 11/Apr/19 03:45
• Updated: 01/Mar/23 19:17
• Resolved: 24/Dec/22 00:56

Description

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 :

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")

result :

Expected :1500-04-30 12:00:00.0
Actual :1500-04-20 12:00:00.0

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 :

if (j < 2299161) {
   j = day + (153 * m + 2) / 5 + 365 * y + y / 4 - 32083;
}

 

Issue Links

• causes: FLINK-11935 Remove DateTimeUtils pull-in and fix datetime casting problem
• is depended upon by: CALCITE-5414 Use DateTimeUtils to correctly convert between java.sql types and Unix timestamps
• is related to: CALCITE-1884 DateTimeUtils produces incorrect results for days before Gregorian cutovers
• links to: GitHub Pull Request #197