calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julian Hyde <jh...@apache.org>
Subject Re: What type is DATE + INTERVAL?
Date Thu, 16 Feb 2017 21:59:18 GMT
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
View raw message