[
https://issues.apache.org/jira/browse/OPTIQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14038272#comment-14038272
]
Julian Hyde commented on OPTIQ-304:
-----------------------------------
Our implementation of DATETIME + INTERVAL is pretty faithful to the SQL
standard... and the SQL standard handles these types very well, in my opinion.
Consider UNIX times. To use them, you have to know that they are integers, that
the granularity is 1 millisecond, and that the epoch is 1970-01-01.
SQL datetimes don't have a preferred epoch or granularity. When you subtract
two datetimes, you get an interval, and you have to specify what the unit of
that interval is (seconds, days, years, etc.) For example, if you want to
convert a unix time T to a SQL timestamp, you write
{{TIMESTAMP '1970-01-01 00:00:00' + T * INTERVAL '0.001' SECOND}}
Not having a preferred epoch or granularity is really elegant, in my opinion.
Now, the various interval types fall into two groups - those in terms of
seconds, and those in terms of months. (There are several kinds of apples, and
several kinds of oranges, but there are no bananas or pineapples.) Day, hour,
minute are just multiples of second (and SQL allows you syntactic sugar - you
can write INTERVAL '10:30' as a shorthand for (10 * 60 + 30) * INTERVAL '1'
SECOND) and year is just a multiple of month. You can't translate a number of
months to a number of days because months have a variable number of days.
{{t + INTERVAL '31' DAY}} and {{t + INTERVAL '1' MONTH}} will yield the same
result if t is '2014-01-01' but a different result if t is '2014-02-01'.
Going back to your apples and oranges. {{date '2014-06-19' + interval '1'
year}} should be implemented internally as {{date '2014-06-19' + 12 * interval
'1' month}}. If it is implemented internally as {{date '2014-06-19' + 12 *
interval '1' day}}, that is a bug.
Some people ask why we even need an interval type. Can't the difference between
two datetimes just be a datetime? It's very similar to pointer arithmetic in C
or C++. When interviewing C programmers, I used to ask: how do you find the mid
point between two pointers, int *p and int *q. The answer is p + (q - p) / 2.
Surprisingly, you can't say (p + q) / 2, because pointers can't be added, only
subtracted. Datetimes behave the same way (there's no epoch, see?).
Re your question. How useful is {{cast(INTERVAL '5' minute as
decimal(2,1))==5}}? It's a convenient way to convert an interval to a number.
It's the same as dividing by the unit interval, {{INTERVAL '5' MINUTE / 5 *
INTERVAL '1' MINUTE == 5}}, but a bit shorter to type. The type determines what
is the unit interval (minute in your first example, hour in the next) and
therefore the multiplier to use.
> Support '<DATE> + <INTEGER>' operator
> -------------------------------------
>
> Key: OPTIQ-304
> URL: https://issues.apache.org/jira/browse/OPTIQ-304
> Project: optiq
> Issue Type: Bug
> Reporter: Julian Hyde
>
> We should support '<DATE> + <INTEGER>' operator. I'm not sure whether it is
> standard SQL, but it occurs in TPC-DS (query 72).
> Note that {{d + n}} is equivalent to {{d + interval n day}} if {{n}} is a
> constant.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
