[
https://issues.apache.org/jira/browse/CALCITE-4837?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17425766#comment-17425766
]
Sergey Nuyanzin commented on CALCITE-4837:
------------------------------------------
Agree, I also have a look across dbs. PostgreSQL[1] and Vertica[2] support
{{DECADE}}, {{CENTURY}}, {{MILLENNIUM}}
And for both the definition is the same
{{DECADE}} - The year field divided by 10
{{CENTURY}} - The first century starts at 0001-01-01 00:00:00 AD, although they
did not know it at the time. This definition applies to all Gregorian calendar
countries. There is no century number 0, you go from -1 century to 1 century.
{{MILLENNIUM}} - The millennium number, where the first millennium is 1 and
each millenium starts on 01-01-y001. For example, millennium 2 starts on
01-01-1001.
[1]
https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
[2]
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/DATE_PART.htm
> 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)
