[
https://issues.apache.org/jira/browse/CALCITE-4837?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17425628#comment-17425628
]
Alessandro Solimando commented on CALCITE-4837:
-----------------------------------------------
We seem to follow a different definition for "to decade"/"to millenium" w.r.t.
that of most other DBs:
* [Redshift
docs|https://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html]
* [JOOQ
docs|https://www.jooq.org/doc/3.1/manual/sql-building/column-expressions/datetime-functions/decade-function/]
(covering with examples most DBs)
For instance, "decade" of "2020" is "202" for almost everyone, except us.
Did you find other systems conforming to the expected behaviour you show in the
ticket?
I agree with you around floor/ceil behaviour, I am rather a bit puzzled by the
"to *" semantics, sorry if not strictly related to the ticket but I think it's
valuable clarification.
> 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)
