[CALCITE-759] Add DayOfWeek and other missing date/time functions

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: babel
• Labels: dialect newbie
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 15/Jun/15 19:27
• Updated: 31/Mar/23 06:17

Description

Calcite implements EXTRACT, FLOOR, CEIL, CAST, +, - on date/time values and much can be accomplished with these. But there are other useful functions in other databases.

For example MySQL has DayOfWeek. See https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html. It is tricky to achieve the same in Calcite (you'd need to subtract the epoch and take the interval modulo 7).

We need to review the date/time functions in MySQL, Postgres and Oracle, and add functions to ensure that you can accomplish the same things in Calcite fairly easily.

Issue Links

• relates to: CALCITE-5180 Implement BigQuery functions for DATE, TIME, TIMESTAMP, DATETIME
• relates to: CALCITE-3412 FLOOR(timestamp TO WEEK) gives wrong result
• relates to: CALCITE-5619 Support PostgreSQL's TO_CHAR function

Sub-Tasks

• Implement TO_TIMESTAMP(value, [format]) function: Open