calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <jhyde.apa...@gmail.com>
Subject Re: What type is DATE + INTERVAL?
Date Thu, 16 Feb 2017 07:48:01 GMT
Funny you should ask. I went through the exact same exercise yesterday, in https://github.com/apache/calcite/commit/d8c4d73fe9bd188ebc004e11ef0f48d934124d8c
<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