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 <[email protected]> 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 <[email protected]> > 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 <[email protected]> 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 >> >>
