[
https://issues.apache.org/jira/browse/CALCITE-4837?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17425633#comment-17425633
]
Alessandro Solimando commented on CALCITE-4837:
-----------------------------------------------
We seem to differ for the "to *" semantics w.r.t. all the other DBs:
* [AWS
Redshift|https://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html]
*
[JOOQ|https://www.jooq.org/doc/3.1/manual/sql-building/column-expressions/datetime-functions/decade-function/]
(covering lots of DBs in the example)
For instance, "to decade" of "2020" is "202" for most DBs.
I agree with you for the floor/ceil behaviour, I am rather puzzled by the "to
*" semantics, do you have any example that behaves like we do or supporting
documentation?
I am just curious and it's not strictly related to the current ticket, but I
think it's worth understanding it better.
> 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)
