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