You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Sergey Nuyanzin (JIRA)" <ji...@apache.org> on 2018/05/25 07:33:00 UTC
[jira] [Comment Edited] (CALCITE-759) Add DayOfWeek and other
missing date/time functions
[ https://issues.apache.org/jira/browse/CALCITE-759?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16490251#comment-16490251 ]
Sergey Nuyanzin edited comment on CALCITE-759 at 5/25/18 7:32 AM:
------------------------------------------------------------------
Hello everyone
if nobody minds I can proceed here as I already did some work within https://issues.apache.org/jira/browse/CALCITE-2303. As far as I see from current doc and code {quote}Useful MySQL functions: DAYOFWEEK, DAYOFMONTH, DAYOFYEAR, QUARTER{quote} are already implemented, a part of {quote}Postgres supports extra time-units in EXTRACT: CENTURY, DECADE, DOW, DOY, EPOCH, ISODOW, ISOYEAR, MICROSECONDS, MILLENNIUM (see http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT).{quote} are also already implemented the other part of EXTRACT is covered in https://issues.apache.org/jira/browse/CALCITE-2303.
however the question is: does it make sense to have synonyms for CENTURY, DECADE, DOW, DOY, EPOCH, ISODOW, ISOYEAR, MICROSECONDS, MILLENNIUM like the existing for YEAR, MONTH and etc.?
To summarize what is not done
# TO_CHAR(datetime, text) and TO_CHAR(interval, text).
# CONVERT and DATEDIFF.
# TO_TIMESTAMP (string date, [string pattern])
# Synonyms for CENTURY, DECADE, DOW, DOY, EPOCH, ISODOW, ISOYEAR, MICROSECONDS, MILLENNIUM?
was (Author: sergey nuyanzin):
Hello everyone
if nobody minds I can proceed here as I already did some work within https://issues.apache.org/jira/browse/CALCITE-2303. As far as I see from current doc and code {quote}Useful MySQL functions: DAYOFWEEK, DAYOFMONTH, DAYOFYEAR, QUARTER{quote} are already implemented, a part of {quote}Postgres supports extra time-units in EXTRACT: CENTURY, DECADE, DOW, DOY, EPOCH, ISODOW, ISOYEAR, MICROSECONDS, MILLENNIUM (see http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT).{quote} are also already implemented the other part of EXTRACT is covered in https://issues.apache.org/jira/browse/CALCITE-2303.
To summarize what is not done
# TO_CHAR(datetime, text) and TO_CHAR(interval, text).
# CONVERT and DATEDIFF.
# TO_TIMESTAMP (string date, [string pattern])
> 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
> Reporter: Julian Hyde
> Assignee: 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
(v7.6.3#76005)