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