[
https://issues.apache.org/jira/browse/CALCITE-2989?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17694246#comment-17694246
]
Gregory Hart commented on CALCITE-2989:
---------------------------------------
[~jdw], could you modify your test case with a date after 1582-10-15? That
should avoid the calendar system issue.
The Java standard is to use a java.util.Calendar instance to parse a timestamp
returned from Timestamp.getTime(). You should be able to use Calendar.get() to
retrieve the year, month, day, hour, etc. For the JDBC API, if you do not
provide a Calendar instance then the default one is used which includes the
local timezone (DEFAULT_ZONE in the DateTimeUtils class).
Avatica represents unix timestamps internally as the number of milliseconds
since the unix epoch in UTC. This requires a timezone conversion when
converting from a Long to a Timestamp using the default Calendar.
You should see the same behavior with the major database vendors (such as
Oracle and SQL Server). If you do not specify a Calendar to
ResultSet.getTimestamp(int, Calendar) then the timestamp is converted to the
local timezone. This allows it be parsed correctly when using the
Calendar.get() methods.
Below is the test case from the merge request that verifies that the unix epoch
as a number (0) is the same as 1970-01-01T00:00:00 UTC.
{code:java}
final Calendar utcCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"),
Locale.ROOT);
utcCal.set(1970, Calendar.JANUARY, 1, 0, 0, 0);
utcCal.set(Calendar.MILLISECOND, 0);
assertThat(unixTimestampToSqlTimestamp(0L, utcCal).getTime(),
is(utcCal.getTimeInMillis()));{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)
