[ https://issues.apache.org/jira/browse/CALCITE-2818?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16780783#comment-16780783 ]
Haisheng Yuan commented on CALCITE-2818: ---------------------------------------- It turns out extracting year,month,day has similar issue. I have opened a PR: https://github.com/apache/calcite/pull/1077 But in misc.iq the test select extract(YEAR from "sqlTimestamp") T from "everyTypes" where extract(YEAR from "sqlTimestamp") IN (1969, 1970); returns 1969 instead of the expected value 1970. My timezone is CST. When evaluating extract(YEAR from "sqlTimestamp"), it uses value 1969-12-31 18:00:00 local timezone (CST), but when printing the value out, it shows GMT value, which is 1970-01-01 00:00:00. They should not behave differently. What do you think? [~julianhyde] Previously the test passed without this patch because extract(YEAR from timestamp '1969-12-31 18:00:00') returns 1970, which was wrong. > 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: 10m > 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)