[
https://issues.apache.org/jira/browse/CALCITE-6949?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu resolved CALCITE-6949.
----------------------------------
Resolution: Fixed
Fixed in
https://github.com/apache/calcite/commit/e9c28c64f32ef1f4dfa394325b9e141a026adb1c
Thank you for your contribution [~xuzifu666]
> ClickHouse not support floor date to SECOND/MILLISECOND/MICROSECOND/NANOSECOND
> ------------------------------------------------------------------------------
>
> Key: CALCITE-6949
> URL: https://issues.apache.org/jira/browse/CALCITE-6949
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.39.0
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.40.0
>
>
> ClickHouse not support floor date to SECOND/MILLISECOND/MICROSECOND/NANOSECOND
> like:
> {code:java}
> SELECT floor(\"hire_date\" TO SECOND) FROM \"employee\" {code}
> would error out:
> {code:java}
> ClickHouse does not support FLOOR for time unit: SECOND
> java.lang.RuntimeException: ClickHouse does not support FLOOR for time unit:
> SECOND
> at
> org.apache.calcite.sql.dialect.ClickHouseSqlDialect.unparseFloor(ClickHouseSqlDialect.java:311)
> at
> org.apache.calcite.sql.dialect.ClickHouseSqlDialect.unparseCall(ClickHouseSqlDialect.java:224)
> at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:133)
> at
> org.apache.calcite.sql.pretty.SqlPrettyWriter$FrameImpl.list2(SqlPrettyWriter.java:1302)
> at
> org.apache.calcite.sql.pretty.SqlPrettyWriter$FrameImpl.list(SqlPrettyWriter.java:1283)
> at
> org.apache.calcite.sql.pretty.SqlPrettyWriter.list(SqlPrettyWriter.java:1080)
> {code}
> correct way should convert to：
> {code:java}
> SELECT toStartOfSecond(`hire_date`) FROM `foodmart`.`employee` {code}
>
> map relation is：（can refer to doc:
> https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofsecond）
>
> {code:java}
> floor(\"hire_date\" TO SECOND) ：toStartOfSecond(`hire_date`)
> floor(\"hire_date\" TO MILLISECOND) ：toStartOfMillisecond(`hire_date`)
> floor(\"hire_date\" TO MICROSECOND) ：toStartOfMicrosecond(`hire_date`)
> floor(\"hire_date\" TO NANOSECOND) ：toStartOfNanosecond(`hire_date`) {code}
>
> test it in ClickHouse is also work well.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
