[
https://issues.apache.org/jira/browse/OPTIQ-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14040160#comment-14040160
]
Vladimir Sitnikov commented on OPTIQ-304:
-----------------------------------------
{quote}Whereas you can just convert a interval to the time unit you want, then
convert that to a number.{quote}
SO author requested the solution for Oracle DB (note oracle tag in the
question), and your suggestion does not apply there (see below).
Can you please provide at least one runtime that allows {{cast as interval
second}} and/or {{cast interval as integer}}?
Once again: I find interval division a smart addition to the type system,
however I find it very hard to debug "5 minutes" is equal "5 seconds" (as they
are both equal to 5 when converted to integer).
Here's output from Oracle 11.2.0.3:
{code:sql}
Position: 47, Line: 1, Column: 48
SQL: select cast(interval '1' day as interval minute) from dual
^-- error here --
ORA-00963: unsupported interval type
{code}
{code:sql}
Position: 47, Line: 1, Column: 48
SQL: select cast(interval '1' day as interval second) from dual
^-- error here --
ORA-00963: unsupported interval type
{code}
{code:sql}
Position: 12, Line: 1, Column: 13
SQL: select cast(interval '1' day as integer) from dual
^-- error here -- ORA-00932: inconsistent datatypes: expected
NUMBER got INTERVAL DAY TO SECOND
{code}
{code:sql}
select cast(interval '1' day as interval day to second) from dual;
returns: 1 0:0:0.0 -- Note: the result is just 1 day with no sign of conversion
to seconds or whatever
{code}
{code:sql}
Position: 26, Line: 1, Column: 27
SQL: select interval '1' day / interval '1' second from dual
^-- error here -- ORA-00932: inconsistent
datatypes: expected NUMBER got INTERVAL DAY TO SECOND
{code}
> 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)
