[ https://issues.apache.org/jira/browse/CALCITE-3412?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16951750#comment-16951750 ]
huaicui edited comment on CALCITE-3412 at 10/16/19 12:56 AM: ------------------------------------------------------------- [~danny0405] We have provided a date and want to get the date of the first day of the week,but the result is the date of this Thursday. Mysql Example: If the week starts on Sunday do this: select DATE_ADD(now(), INTERVAL(1-DAYOFWEEK(now())) DAY) If the week starts on Monday do this: select DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY); We use this sql of the calcite to get what we want: select FLOOR(CAST('2017-01-28' AS TIMESTAMP) TO WEEK) But the result is the date of this Thursday. Can you give suggestion to fix that? was (Author: kofiori): We have provided a date and want to get the date of the first day of the week,but the result is the date of this Thursday. Mysql Example: If the week starts on Sunday do this: select DATE_ADD(now(), INTERVAL(1-DAYOFWEEK(now())) DAY) If the week starts on Monday do this: select DATE_ADD(now(), INTERVAL(-WEEKDAY(now())) DAY); We use this sql of the calcite to get what we want: select FLOOR(CAST('2017-01-28' AS TIMESTAMP) TO WEEK) But the result is the date of this Thursday. Can you give suggestion to fix that? > Response Error for FLOOR TIMESTAMP TO WEEK > ------------------------------------------- > > Key: CALCITE-3412 > URL: https://issues.apache.org/jira/browse/CALCITE-3412 > Project: Calcite > Issue Type: Bug > Components: core > Environment: mac,linux > Reporter: huaicui > Priority: Major > Attachments: image-2019-10-15-13-33-34-896.png > > Original Estimate: 24h > Remaining Estimate: 24h > > Floor timestamp to week exist error: > The start day of the week is not Sunday or Monday, calcite use Thursday to > split a week. > Example: > sql: select FLOOR(CAST('2017-01-28' AS TIMESTAMP) TO WEEK); > Response: > 2017-01-26 00:00:00.0 > > 2017-01-26 is Tuesday that is not our expect result. As result, 2017-01-22 is > the first day that we expect. > > -- This message was sent by Atlassian Jira (v8.3.4#803005)