You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Haisheng Yuan (JIRA)" <ji...@apache.org> on 2019/02/28 17:54:00 UTC
[jira] [Commented] (CALCITE-2818) EXTRACT returns wrong results for
DATE and TIMESTAMP values before epoch
[ 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)