calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gian Merlino <g...@imply.io>
Subject Re: What type is DATE + INTERVAL?
Date Thu, 16 Feb 2017 22:11:56 GMT
Yes I have, although it's disabled in Druid for now because of a bug:
https://issues.apache.org/jira/browse/CALCITE-1601. I haven't had time yet
to look into why that's happening.

Gian

On Thu, Feb 16, 2017 at 1:59 PM, Julian Hyde <jhyde@apache.org> wrote:

> Yes, exactly.
>
> FLOOR( .. TO timeUnit) is non-standard, if that matters.
>
> Have you seen DateRangeRules (and DateRangeRulesTest)? The work you are
> doing would fit in perfectly.
>
> Julian
>
>
>
> > On Feb 16, 2017, at 1:28 PM, Gian Merlino <gian@imply.io> wrote:
> >
> > Fair enough, so I guess the way to do that without using FLOOR would be:
> >
> >   CAST(CAST(ts AS DATE) AS TIMESTAMP) + EXTRACT(HOUR FROM ts) * INTERVAL
> > '1' HOUR
> >
> > (Casting to date and back to timestamp to do a poor mans' floor to day)
> >
> > Gian
> >
> > On Wed, Feb 15, 2017 at 11:48 PM, Julian Hyde <jhyde.apache@gmail.com>
> > wrote:
> >
> >> Funny you should ask. I went through the exact same exercise yesterday,
> in
> >> https://github.com/apache/calcite/commit/d8c4d73fe9bd188ebc004e11ef0f48
> >> d934124d8c <https://github.com/apache/calcite/commit/
> >> d8c4d73fe9bd188ebc004e11ef0f48d934124d8c>.
> >>
> >> The answer is that Calcite is right. The SQL:2014 draft standard (In
> >> section 6.31 <datetime value expression>, syntax rules, section 5a.)
> says:
> >>
> >> ​​If the <datetime value expression> immediately contains either <plus
> >> sign> or <minus sign>, then … The result of the <datetime value
> expression>
> >> contains the same <primary datetime field>s that are contained in the
> >> <datetime value expression> or <datetime term>, with a fractional
> seconds
> >> precision that is the greater of the fractional seconds precisions, if
> any,
> >> of either the <datetime value expression> and <interval term>, or
the
> >> <datetime term> and <interval value expression> that it simply contains.
> >>
> >> The phrase "contains the same <primary datetime fields>s" means that if
> >> the input contains, say, day, then the output will contain just day,
> >> regardless of what the interval contains.
> >>
> >> I think Postgres is trying to be helpful upgrading date to timestamp.
> But
> >> I think the standard is the right behavior, because it gives predictable
> >> behavior and more control to the user. For instance, if you want the
> result
> >> to be a timestamp, cast the argument to a timestamp before you add the
> >> interval. For example,
> >>
> >> DATE ‘2017-02-16' + INTERVAL ’25’ HOUR yields DATE ‘2017-02-17’
> >> CAST(DATE ‘2017-02-16’ AS TIMESTAMP) + INTERVAL ’25’ HOUR yields
> >> TIMESTAMP ‘2017-02-18 01:00:00’.
> >>
> >> Julian
> >>
> >>
> >>
> >>> On Feb 15, 2017, at 11:23 PM, Gian Merlino <gian@imply.io> wrote:
> >>>
> >>> I've run into SQL in the wild that did something like:
> >>>
> >>>  CAST(ts AS DATE) + EXTRACT(HOUR FROM ts) * INTERVAL '1' HOUR
> >>>
> >>> Which the user expected to mean the same thing as:
> >>>
> >>>  FLOOR(ts TO HOUR)
> >>>
> >>> I started writing a planner rule to make the former work, but ran into
> >> type
> >>> errors since Calcite treats its type as DATE not TIMESTAMP. Postgres
> >> seems
> >>> to treat it as a TIMESTAMP and does what the user had in mind with the
> >>> original SQL:
> >>>
> >>> # SELECT CAST(TIMESTAMP '2000-01-01 04:11:22' AS DATE) + EXTRACT(HOUR
> >> FROM
> >>> TIMESTAMP '2000-01-01 04:11:22') * INTERVAL '1' HOUR;
> >>>     ?column?
> >>> ---------------------
> >>> 2000-01-01 04:00:00
> >>> (1 row)
> >>>
> >>> Who's right?
> >>>
> >>> Gian
> >>
> >>
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message