I have logged https://issues.apache.org/jira/browse/CALCITE-2339 <https://issues.apache.org/jira/browse/CALCITE-2339> to capture this discussion, and attached a PDF snapshot of the google doc.
> On May 30, 2018, at 1:24 PM, James Duong <[email protected]> wrote: > > Note that this is essentially an extension of the current way we expose > these two operations. > > We expose a form that normalizes the two approaches but allows the adapter > layer > to get it into a form that it supports. Some databases support TIMESTAMPADD > only > such as SQL Server. While others support Datetime_plus only such as Oracle. > > On Wed, May 30, 2018 at 7:32 AM, James Duong <[email protected]> wrote: > >> Thanks Michael. >> >> Here is a link to a Google Doc: >> https://docs.google.com/document/d/1j0wa0pZ2senQhAoy_ >> 3W_Ev2vHqIhm3ob19BGy4nBJkI/edit?usp=sharing >> >> On Wed, May 30, 2018 at 6:08 AM, Michael Mior <[email protected]> wrote: >> >>> James, >>> >>> Just a note that the list doesn't support attachments. I'd suggest >>> creating >>> a Google Doc with the contents. >>> >>> -- >>> Michael Mior >>> [email protected] >>> >>> >>> Le mer. 30 mai 2018 à 04:23, James Duong <[email protected]> a écrit : >>> >>>> I've recorded my thoughts on this in the attached document >>>> >>>> A few notes: >>>> - TimestampAdd has very well-spec'd behavior as part of ODBC. For >>> example >>>> the semantics for how to handle adding a second to a date value are >>> clearly >>>> defined. I experimented with a few different databases for how this is >>>> handled with datetime_plus and didn't find it consistent. >>>> - Transforming a TimestampAdd expression to datetime_plus would require >>>> multiplying a 'unit' interval by the expression used for the interval >>>> parameter in TimestampAdd. This looks odd in generated SQL, and if you >>> were >>>> go to back to timestampadd, the original intent wouldn't be clear. >>>> - There are a few units in timestampadd that are not directly supported >>> by >>>> intervals (week, quarter, millennium, etc) >>>> >>>> So I wouldn't recommend datetime_plus. I'd recommend a new structure >>>> similar to timestampadd, but with features to transform to >>> datetime_plus. >>>> >>>> On Mon, May 28, 2018 at 6:52 PM, Julian Hyde <[email protected]> wrote: >>>> >>>>> I think we should use the datetime_plus operator. It is standard, and >>>>> sufficiently general. Its second argument needs to be an interval >>>>> value, not necessarily an interval literal. >>>>> >>>>> On Mon, May 28, 2018 at 2:35 PM, James Duong <[email protected]> >>> wrote: >>>>>> There are essentially two ways to add an interval to a datetime vaue >>> in >>>>>> Calcite >>>>>> >>>>>> Call the timestampadd() function: >>>>>> select {fn timestampadd(year, 1, hire_date)}... >>>>>> >>>>>> Use datetime_plus interval arithmetic: >>>>>> select hire_date + interval '1' year >>>>>> >>>>>> >>>>>> Note that timestampadd's second argument does not need to be a >>> literal. >>>>>> Often it is a column expression. For datetime_plus literals are >>> usually >>>>>> used. >>>>>> >>>>>> I propose we create a new SqlOperator that can canonicalize both of >>>>> these >>>>>> inputs into one node. This lets us apply any transformations on this >>>>>> canonical type regardless of what the original query was. >>>>>> >>>>>> It takes in the following arguments: >>>>>> 1. A date/time/timestamp input >>>>>> 2. an interval input as an integer >>>>>> 3. a time unit for the input >>>>>> 4. a synthetic argument indicating the source form of the function >>> call >>>>>> (either datetime_plus or timestampadd). >>>>>> >>>>>> >>>>>> The idea is that this canonical form is easy to get into for both >>> types, >>>>>> and provides methods to easily convert to either type. This would >>> help >>>>> with >>>>>> unparsing in SqlDialects (you do not need to implement pushdown for >>> both >>>>>> types of inputs). >>>>> >>>> >>>> >>> >> >>
