[
https://issues.apache.org/jira/browse/OPTIQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14038516#comment-14038516
]
Vladimir Sitnikov commented on OPTIQ-304:
-----------------------------------------
Thank you for the detailed explanation.
{quote}[~julianhyde]: You can't translate a number of months to a number of
days because months have a variable number of days.{quote}
You can't translate the number of days to the number of hours either :(
I do get why `typeof(DATE - DATE) != typeof(DATE)`, however I am not sure if we
want go down to difference between [duration and
period|http://docs.oracle.com/javase/tutorial/datetime/iso/period.html]
{quote}JDK on Duration vs Period: A Duration of one day is exactly 24 hours
long. A Period of one day, when added to a ZonedDateTime, may vary according to
the time zone. For example, if it occurs on the first or last day of daylight
saving time.{quote}
{quote}[~julianhyde]: How useful is cast(INTERVAL '5' minute as
decimal(2,1))==5? It's a convenient way to convert an interval to a
number{quote}
Can you please provide at least one business use case for that convenience?
My point is: it is very error prone API since someone might unexpectedly
convert interval to numeric and _lose_ the units part.
Oracle DB does not allow converting INTERVALS to numerics. I believe PostgreSQL
does not allow it either.
Division interval by interval is fine. Blind cut of the unit is not.
I guess division it is an extension, thus we might need some workaround when
pushing interval divisions to downstream JDBC.
As far as I know, the standard way of converting intervals to numerics is
{{extract(minute from ...)}}. This makes clear what is the unit of output
numeric.
{quote}[~julianhyde]: If it is implemented internally as date '2014-06-19' + 12
* interval '1' day, that is a bug.{quote}
There is a bug. I've discovered it while creating tests for window frame
{{range '1' year preceding}}: {{JdbcTest.testWinIntervalFrame}}.
> 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)
