[jira] [Created] (CALCITE-2818) Implementation of Extract (ExtractImplementor) returns wrong result for dates before Epoch.

["Mickaël Sauvée (JIRA)" <ji...@apache.org>]

Mickaël Sauvée created CALCITE-2818:
---------------------------------------

             Summary: Implementation of Extract (ExtractImplementor) returns wrong result for dates before Epoch. 
                 Key: CALCITE-2818
                 URL: https://issues.apache.org/jira/browse/CALCITE-2818
             Project: Calcite
          Issue Type: Bug
            Reporter: Mickaël Sauvée
            Assignee: Julian Hyde


Computation is based on Java modulo that have a certain behaviour on negative number.

For extracting hour, minutes and seconds, the computation is wroing. HJere is an example on hour extract.

30-12-1969T21:13:20+0 is -100 000 000 in milisec relative to EPoch.

(-100 000 000 % 86 400 000) / 3 600 000 = -3,77 , so 3 hour is returned, and it should be 21.

For negative input value, it is required to add unit.multiplier.longValue() (ie. 8 640 000 in hour case) to the value before dividing it.

You can use this test (SQLOperatorBaseTest.java):
 
{code:java}
@Test public void testExtractWithDatesBeforeUnixEpoch() {
  tester.checkScalar(
  "extract(hour from TIMESTAMP '1969-12-31 21:13:20')",
  "21",
  "BIGINT NOT NULL");
}{code}
 



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)