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 21:28:11 GMT
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