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 > >
