Hi, Jaffe Flink follows old version calcite’s behaviour which lead to the wrong behavior. snd Julian is right that calcite has corrected FLOOR and CEIL functions in CALCITE-3412, Flink has upgraded calcite to 1.26 version which contains the patch, what we need is only to adapt it in Flink code, I’d like to make this as a part of FLIP-162 and fix it soon.
Thanks Julian and Timo for the reminder. Best, Leonard > 在 2021年3月3日，03:35，Jaffe, Julian <julianja...@activision.com> 写道： > > 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í > >
