OSDir


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

Re: Datetime_Plus and TimestampAdd operators


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 <jduong@xxxxxxxxxx> 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 <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).
>> >>
>> >
>> >
>>
>
>