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
