[
https://issues.apache.org/jira/browse/CALCITE-2989?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17634493#comment-17634493
]
Gregory Hart commented on CALCITE-2989:
---------------------------------------
The problem is that {{java.sql.Timestamp}} and {{DateTimeUtils}} use different
calendar systems. Here's an example that may help:
{code:java}
// What date comes before 1582-10-15?
int date = DateTimeUtils.ymdToUnixDate(1582, 10, 15) - 1;
DateTimeUtils.unixDateToString(date); // returns "1582-10-14"
long time = date * DateTimeUtils.MILLIS_PER_DAY;
int offset = TimeZone.getDefault().getOffset(time);
new java.sql.Date(time - offset); // returns "1582-10-04"{code}
We use {{DateTimeUtils}} for some conversions and {{new Timestamp()}} for
others. I think {{DateTimeUtils}} follows ANSI SQL and the ISO calendar system
(also called the proleptic Gregorian calendar?), whereas the java.sql classes
use a Julian-Gregorian hybrid that cannot represent dates from 1582-10-05 to
1582-10-14. I think the main question is whether we want our unix timestamps in
the Julian-Gregorian hybrid used by java.sql or the ISO calendar system used by
ANSI SQL? I can add this explanation to {{{}toLong{}}}.
The use of {{LocalDateTime}} was to avoid the deprecated methods of
{{{}java.util.Date{}}}. It's faster than constructing a new
{{GregorianCalendar}} every time, which is the class that replaced those
methods.
> The method ymdToJulian in DateTimeUtils returns wrong value
> -----------------------------------------------------------
>
> Key: CALCITE-2989
> URL: https://issues.apache.org/jira/browse/CALCITE-2989
> Project: Calcite
> Issue Type: Bug
> Components: avatica
> Reporter: vinoyang
> Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> 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)
