[ https://issues.apache.org/jira/browse/CALCITE-759?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17681171#comment-17681171 ]
Julian Hyde edited comment on CALCITE-759 at 1/27/23 3:39 AM: -------------------------------------------------------------- [~mbudiu], It seems that you are right. Postgres {{EXTRACT(DOW FROM ...)}} returns 0 (Sunday) through 6 (Saturday). Can you log a separate case for this, and survey the behavior in other databases? If Calcite is the only one that returns 1 .. 7 then we should probably change. (Support for DOW in EXTRACT was added in CALCITE-1613.) Regarding the {{DayOfWeek}} function. It seems that MSSql and MySQL return 1 (Sunday) through 7 (Saturday) and Calcite is consistent with that. was (Author: julianhyde): [~mbudiu], It seems that you are right. Postgres {{EXTRACT(DOW FROM ...)}} returns 0 (Sunday) through 6 (Saturday). Can you log a separate case for this, and survey the behavior in other databases? If Calcite is the only one that returns 1 .. 7 then we should probably change. Regarding the {{DayOfWeek}} function. It seems that MSSql and MySQL return 1 (Sunday) through 7 (Saturday) and Calcite is consistent with that. > Add DayOfWeek and other missing date/time functions > --------------------------------------------------- > > Key: CALCITE-759 > URL: https://issues.apache.org/jira/browse/CALCITE-759 > Project: Calcite > Issue Type: Bug > Components: babel > Reporter: Julian Hyde > Priority: Major > Labels: dialect, newbie > > 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. -- This message was sent by Atlassian Jira (v8.20.10#820010)