[
https://issues.apache.org/jira/browse/CALCITE-4837?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17425669#comment-17425669
]
Sergey Nuyanzin commented on CALCITE-4837:
------------------------------------------
{quote}For instance, "decade" of "2020" is "202" for almost everyone, except
us.{quote}
In fact there are 3 possible functions here: {{EXTRACT}}, {{FLOOR}} and
{{CEIL}}.
{{EXTRACT}} does the thing as mentioned in links you provided, i.e.
{code:sql}
select extract(decade from date '2020-02-03'), extract(decade from date
'2021-10-07');
+--------+--------+
| EXPR$0 | EXPR$1 |
+--------+--------+
| 202 | 202 |
+--------+--------+
{code}
>From the other side there are {{FLOOR}} and {{CEIL}} in Calcite. Frankly
>speaking I do not know the history how they appeared.
However the current documentation[1] says
{noformat}
| FLOOR(datetime TO timeUnit) | Rounds *datetime* down to *timeUnit*
| CEIL(datetime TO timeUnit) | Rounds *datetime* up to *timeUnit*
{noformat}
so it should be timeunit. And existing logic for {{YEAR}}, {{QUARTER}},
{{MONTH}} does rounding to the start of year, quarter, month.
[1]
https://github.com/apache/calcite/blob/50dbd12f14f330f57a321a2402a7dcb8124f5112/site/_docs/reference.md#L1353-L1354
> 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)
