Calcite does not follow ISO-8601. Instead, until very recently Calcite weeks started on Thursdays[1].
(As an aside, Calcite somewhat abuses the WEEK time unit - converting a date to a week returns an integer representing the week of the year the date falls in while FLOORing or CEILing a timestamp to week returns a timestamp. This can cause integration issues with other systems if you're unaware) Julian [1] https://issues.apache.org/jira/browse/CALCITE-3412 On 3/2/21, 4:12 AM, "Timo Walther" <twal...@apache.org> wrote: Hi Sebastián, it might be the case that some time functions are not correct due to the underlying refactoring of data structures. I will loop in Leonard in CC that currently works on improving this situation as part of FLIP-162 [1]. @Leonard: Is this wrong behavior on your list? Regards, Timo [1] https://urldefense.proofpoint.com/v2/url?u=https-3A__cwiki.apache.org_confluence_display_FLINK_FLIP-2D162-253A-2BConsistent-2BFlink-2BSQL-2Btime-2Bfunction-2Bbehavior&d=DwIDaQ&c=qE8EibqjfXM-zBfebVhd4gtjNZbrDcrKYXvb1gt38s4&r=zKznthi6OTKpoJID9dIcyiJ28NX59JIQ2bD246nnMac&m=ZeBr2XK222xGShmn_0N2tF_qGbi7kvWg8WQrsLuquMk&s=3-zBtwyRTd7WiU63ZVwpTKW4vDnn-fjckRI9yjFjrNs&e= On 02.03.21 12:26, Sebastián Magrí wrote: > While using a simple query such as this > > SELECT > `ts`, > FLOOR(`ts` TO WEEK) as `week_start`, > CEIL(`ts` TO WEEK) as `week_end` > FROM some_table > > I get some weird results like these: > > 2021-03-01T00:00| 2021-02-25T00:00| 2021-03-04T00:00 > > Which is obviously wrong since March 1st is on Monday, February 25th is > Thursday as well as March 04th. > > I've tried different combinations of timezone configurations and with > both timestamps and dates, with the same results. > > Is there anything obviously wrong in that query? Is there any > configuration to keep in mind for the start of week day? > > -- > Sebastián Ramírez Magrí
