OSDir

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Datetime_Plus and TimestampAdd operators


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 <mmior@xxxxxxxxxxxx> wrote:

> James,
>
> Just a note that the list doesn't support attachments. I'd suggest creating
> a Google Doc with the contents.
>
> --
> Michael Mior
> mmior@xxxxxxxxxxxx
>
>
> Le mer. 30 mai 2018 à 04:23, James Duong <jduong@xxxxxxxxxx> 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 <jhyde@xxxxxxxxxx> 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 <jduong@xxxxxxxxxx> 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).
> >>
> >
> >
>