[
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)
