[ https://issues.apache.org/jira/browse/CALCITE-2818?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16788244#comment-16788244 ]
Andrei Sereda commented on CALCITE-2818: ---------------------------------------- [~julianhyde] While reviewing this PR I have noticed that {{extract}} function has different semantics depending on vendor. {code:sql} extract(millisecond from TIMESTAMP '1969-12-31 21:13:17.357') {code} Will return {{357}} in Oracle and SQL Server but {{17357}} in Postgres. Do you think implementation should be different depending on current dialect ? This is a separate discussion not related to current PR. > EXTRACT returns wrong results for DATE and TIMESTAMP values before epoch > ------------------------------------------------------------------------ > > Key: CALCITE-2818 > URL: https://issues.apache.org/jira/browse/CALCITE-2818 > Project: Calcite > Issue Type: Bug > Reporter: Mickaël Sauvée > Priority: Major > Labels: easyfix, pull-request-available > Time Spent: 1h 50m > Remaining Estimate: 0h > > Implementation of Extract (ExtractImplementor) returns wrong result for dates > before Epoch. > Computation is based on Java modulo that have a certain behavior on negative > number. > For extracting hour, minutes and seconds, the computation is wrong. > Here is an example on hour extract with the date 30-12-1969T21:13:20+0 is > -100 000 000 in milliseconds relative to unix 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)