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í


Reply via email to