calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gian Merlino <g...@imply.io>
Subject What type is DATE + INTERVAL?
Date Thu, 16 Feb 2017 07:23:27 GMT
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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message