flink-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From snuyanzin <...@git.apache.org>
Subject [GitHub] flink issue #6007: [FLINK-8518][Table API & SQL] Support DOW for EXTRACT
Date Wed, 23 May 2018 11:06:13 GMT
Github user snuyanzin commented on the issue:

    @twalthr thank you for your comment.
    I took a similar ticket in Calcite/Avatica from which the current depends on and I pointed
this discrepancy in the [comment ](https://issues.apache.org/jira/browse/CALCITE-2303?focusedCommentId=16480420&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-16480420).
To fix i  t the class `org.apache.calcite.avatica.util.DateTimeUtils` should be changed which
is presented in both avatica and flink.
    At the same time different db's provide a little bit different behavior (please have a
look at the links below)
    looks like in case of day of week extraction for Oracle, MySql Sunday = 1, for Postgresql
Sunday = 0, for Microsoft SQL Server it depends on property set by user
    On the other hand there is a standard [ISO8601](https://en.wikipedia.org/wiki/ISO_week_date)
which also defines weekday, day of years e.g. This is also a part of [CALCITE-2303](https://issues.apache.org/jira/browse/CALCITE-2303).
    So my suggestion is within this ticket provide support for all operations which could
come from [CALCITE-2303](https://issues.apache.org/jira/browse/CALCITE-2303): `dow`, `decade`,
`epoch`, `isodow`, `isoyear`, `microsecond` and `millisecond`
    However yes you are right it is required to choose what approach for dayOfWeek to use.
IMHO the simplest way is to use whatever Calcite/avatica provides
    At the same time [here](https://issues.apache.org/jira/browse/CALCITE-2303?focusedCommentId=16482767&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-16482767)
 Julian Hyde says that 
    > In short, yes, do whatever PostgreSQL does. 
    So they would like to align the behavior with Postrgresql
    About different db's day of week
    So the Postgresql's behavior is described [here ](https://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)
with Sunday = 0 + it supports ISO8601 via `isodow `and `isoyear`
    the Oracle's behavior is described [here ](https://docs.oracle.com/cd/E37483_01/server.751/es_eql/src/ceql_functions_date_extract.html)
with Sunday = 1, so far have not found info about support of 8601 via `extract` while it is
via `to_char/to_date`
    Microsoft SQL Server [allows to set up the first weekday](https://docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-2017)
at the same time extraction is done via `datepart` not `extract`
    MySQL [provides weekday](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract)
with Sunday = 1


View raw message