[ https://issues.apache.org/jira/browse/CALCITE-4837?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17425769#comment-17425769 ]
Julian Hyde commented on CALCITE-4837: -------------------------------------- Ok, so the following formulas probably work: * {{millennium(y) = floor((y + 999) / 1000)}}, * {{century(y) = floor((y + 99) / 100)}}. Just in case people misunderstand what you wrote. For SQL's purposes, it doesn't matter whether we are in a 'Gregorian calendar country'. EXTRACT, FLOOR and CEIL do not consult timezone or locale, just work on a zoneless DATE or TIMESTAMP value. > FLOOR and CEIL of DATE/TIMESTAMP return wrong results for DECADE, CENTURY and > MILLENNIUM > ---------------------------------------------------------------------------------------- > > Key: CALCITE-4837 > URL: https://issues.apache.org/jira/browse/CALCITE-4837 > Project: Calcite > Issue Type: Bug > Components: avatica, core > Reporter: Sergey Nuyanzin > Priority: Major > Time Spent: 10m > Remaining Estimate: 0h > > The query to reproduce > {code:sql} > select floor(t to decade) as floor_decade, > ceil(t to decade) as ceil_decade, > floor(t to century) as floor_century, > ceil(t to century) as ceil_century, > floor(t to millennium) as floor_millennium, > ceil(t to millennium) as ceil_millennium > from (values(date '2021-10-07')) t; > {code} > it produces output > {noformat} > +--------------+-------------+--------------+--------------+------------------+-----------------+ > | FLOOR_DECADE | CEIL_DECADE | FLOOR_CENTURY | CEIL_CENTURY | > FLOOR_MILLENNIUM | CEIL_MILLENNIUM | > +--------------+-------------+--------------+--------------+------------------+-----------------+ > | 2021-08-01 | 2021-11-29 | 2019-04-14 | 2022-07-27 | 2002-11-09 > | 2035-09-17 | > +--------------+-------------+--------------+--------------+------------------+-----------------+ > {noformat} > expected > {noformat} > +--------------+-------------+--------------+--------------+------------------+-----------------+ > | FLOOR_DECADE | CEIL_DECADE | FLOOR_CENTURY | CEIL_CENTURY | > FLOOR_MILLENNIUM | CEIL_MILLENNIUM | > +--------------+-------------+--------------+--------------+------------------+-----------------+ > | 2020-01-01 | 2030-01-01 | 2000-01-01 | 2100-01-01 | 2000-01-01 > | 3000-01-01 | > +--------------+-------------+--------------+--------------+------------------+-----------------+ > {noformat} -- This message was sent by Atlassian Jira (v8.3.4#803005)