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/06/02 14:21:00 UTC
[jira] [Commented] (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=16499089#comment-16499089 ]
Sergey Nuyanzin commented on CALCITE-759:
-----------------------------------------
Ok, I see
there is the initial attempt to do such review (I do not include functions which already present in master's calcite/avatica).
based on [MySQL|https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html], [SQL Server|https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017], [Oracle|https://docs.oracle.com/database/121/NLSPG/ch4datetime.htm#GUID-429C290A-C201-4272-A369-FF8F4F8E54F7] and [PostgreSQL|https://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT]
I am not sure that this this the final list however please let me know if I am on the right way or not. Also is yes I could take participant at least in part of its implementation however IMHO it would be nice to divide it on some subtasks and answer which are more important and which are less
||Name||Microsoft SQL Server||MySQL||Oracle||PostgreSQL||Comment||
|AGE| | | |+|age(timestamp, timestamp) Subtract arguments, producing a "symbolic" result that uses years and months{code:java}
age(timestamp '2001-04-10', timestamp '1957-06-13') 43 years 9 mons 27 days{code}
age(timestamp) Subtract from current_date (at midnight){code:java}
age(timestamp '1957-06-13') 43 years 8 mons 3 days{code}|
|DATEADD|+| | | |DATEADD (datepart , number , date ) Returns a new datetime value by adding an interval to the specified datepart of the specified date. The data type of the date argument|
|DATEFROMPARTS|+| | | |DATEFROMPARTS ( year, month, day ) Returns a date value for the specified year, month, and day.|
|DATETIME2FROMPARTS|+| | | |DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision) Returns a datetime2 value for the specified date and time, with the specified precision.|
|DATETIMEFROMPARTS|+| | | |DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds) Returns a datetime value for the specified date and time.|
|DATETIMEOFFSETFROMPARTS|+| | | |DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision) Returns a datetimeoffset value for the specified date and time, with the specified offsets and precision.|
|DATEPART|+| | |+|could be done as synonym for extract based on PostrgreSQL|
|DAY|+|+| | |could be done as synonym for DAYOFMONTH based on MySQL docs|
|DATE_TRUNC| | | |+|Truncate to specified precision; see also Section 9.9.2{code:java}
date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00{code}|
|DATE| |+| | |Extract the date part of a date or datetime expression|
|DATEDIFF|+| | | |Returns the number of date or time datepart boundaries, crossed between two specified dates. Returns int|
|DATEDIFF_BIG|+| | | |Returns the number of date or time datepart boundaries, crossed between two specified dates. Returns bigint|
|DATE_SUB| |+| | |Subtract a time value (interval) from a date|
|DATEDIFF| |+| | |Subtract two dates|
|TIMEDIFF| |+| | |Subtract time|
|TIMESTAMPDIFF| |+| | |Subtract an interval from a datetime expression|
|PERIOD_DIFF| |+| | |Return the number of months between periods|
|SUBDATE| |+| | |Synonym for DATE_SUB() when invoked with three arguments|
|SUBTIME| |+| | |Subtract times|
|EOMONTH|+| | | |EOMONTH ( start_date [, month_to_add ] ) Returns the last day of the month containing the specified date, with an optional offset. Return type is the type of the start_date argument, or alternately, the date data type|
|LAST_DAY| |+|+| |Returns the last day of the month that contains date|
|NEXT_DAY| | |+| |Returns the date of the first weekday named by char that is later than date|
|MICROSECOND| |+| | |Return the microseconds from argument|
|MONTHNAME| |+| | |Return the name of the month|
|TIME| |+| | |Extract the time portion of the expression passed|
|SEC_TO_TIME| |+| | |Converts seconds to 'HH:MM:SS' format|
|MAKEDATE| |+| | |Create a date from the year and day of year|
|MAKETIME| |+| | |Create time from hour, minute, second|
|FROM_DAYS| |+| | |Convert a day number to a date|
|FROM_UNIXTIME| |+| | |Format Unix timestamp as a date|
|NUMTODSINTERVAL| | |+| |Converts number n to an INTERVAL DAY TO SECOND literal|
|NUMTOYMINTERVAL| | |+| |Converts number n to an INTERVAL YEAR TO MONTH literal|
|ROUND| | |+| |Returns date rounded to the unit specified by the fmt format model|
|TRUNC| | |+| |Returns date with the time portion of the day truncated to the unit specified by the fmt format model|
|JUSTIFY_DAYS| | | |+|Adjust interval so 30-day time periods are represented as months{code:java}
justify_days(interval '35 days') 1 mon 5 days{code}|
|JUSTIFY_HOURS| | | |+|Adjust interval so 24-hour time periods are represented as days{code:java}
justify_hours(interval '27 hours') 1 day 03:00:00{code}|
|JUSTIFY_INTERVAL| | | |+|Adjust interval using justify_days and justify_hours, with additional sign adjustments{code:java}
justify_interval(interval '1 mon -1 hour') 29 days 23:00:00{code}|
|TO_CHAR| | |+| +|Converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE data type to a value of VARCHAR2 data type in the format specified by the fmt date format|
|TO_DSINTERVAL| | |+| |Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of INTERVAL DAY TO SECOND data type|
|TO_NCHAR| | |+| |Converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL MONTH TO YEAR, or INTERVAL DAY TO SECOND data type from the database character set to the national character set|
|TO_TIMESTAMP| | |+|+ |Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of TIMESTAMP data type|
|TO_TIMESTAMP_TZ| | |+| |Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the TIMESTAMP WITH TIME ZONE data type|
|TO_YMINTERVAL| | |+| |Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL YEAR TO MONTH data type|
also there could be some questions about these functions
|| Name || Description ||
|WEEKDAY()| Return the weekday index|
|WEEKOFYEAR()| Return the calendar week of the date (1-53)|
|YEARWEEK() | Return the year and week |
what standard should be used here ODBC, ISO-8601 whatever else?
> 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)