[CALCITE-3412] FLOOR(timestamp TO WEEK) gives wrong result

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: avatica-1.16.0, 1.23.0
• Component/s: avatica, core
• Assignee: Julian Hyde
• Reporter: huaicui
• Created: 15/Oct/19 05:34
• Updated: 24/May/20 12:00
• Resolved: 11/Mar/20 03:26

Description

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 Thursday that is not our expect result. As result, 2017-01-22 is the first day that we expect.

 

 

Issue Links

• depends upon: CALCITE-3398 Release avatica 1.16.0
• is related to: CALCITE-759 Add DayOfWeek and other missing date/time functions
• is related to: CALCITE-2303 In EXTRACT function, support MICROSECONDS, MILLISECONDS, EPOCH, ISODOW, ISOYEAR and DECADE time units