[jira] [Commented] (CALCITE-1832) DateTimeUtils dateString to Unix translation error

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1832?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16041297#comment-16041297 ] 

Julian Hyde commented on CALCITE-1832:
--------------------------------------

If doesn't "add an offset of 10 days". It fails to subtract an offset of 10 days, intentionally so. The offset in question is the shift from Julian to Gregorian calendar that happened in 1752. Consistent with the SQL standard, SQL dates are always in [New Style|https://en.wikipedia.org/wiki/Old_Style_and_New_Style_dates] for dates before 1752, which were using Old Style.

Postgres [explains it well|https://www.postgresql.org/docs/9.2/static/datetime-units-history.html].

Unix (or rather its {{cal}} program) shifts from Old-style to New-style, like this:

{noformat}
$ cal 9 1752
   September 1752
Su Mo Tu We Th Fr Sa
       1  2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
{noformat}

But SQL sticks with New-style:

{noformat}> VALUES DATE '1752-09-20' - INTERVAL '10' DAY;
+--------+
| EXPR$0 |
+--------+
| 1752-09-10 |
+--------+
1 row selected (0.132 seconds){noformat}

If you want Old-Style dates I guess you could write a UDF that subtracts 11 days and adds the missing century leap days.

> DateTimeUtils dateString to Unix translation error
> --------------------------------------------------
>
>                 Key: CALCITE-1832
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1832
>             Project: Calcite
>          Issue Type: Bug
>          Components: avatica
>    Affects Versions: 1.10.0
>            Reporter: Stefano Bortoli
>
> Using the latest snapshot of Calcite, one of the Apache Flink Test fails. The failure is due to an error in CAST, that relies on Avatica DateTimeUtils.
> I have implemented a simple class test:
> {code}
> public class Test 
> {
>     public static void main( String[] args )
>     {
>        int dateInt = DateTimeUtils.dateStringToUnixDate("1500-03-04");
>        System.out.println(dateInt);
>        System.out.println(DateTimeUtils.unixDateToString(dateInt));
>        
>     }
> }
> {code}
> and the output is:
> {code}
> -171592
> 1500-03-14
> {code}
> it adds an offset of 10 days for some reason. 



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)