[ https://issues.apache.org/jira/browse/TRAFODION-2582?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15958199#comment-15958199 ]
Hans Zeller commented on TRAFODION-2582: ---------------------------------------- The result we would have expected would show the difference between the timestamps, which is 300 seconds or 5 minutes for the first two queries. The problem (if it is indeed a bug, as I believe) is in BiArith::bindNode. When we see this operation of a difference between two timestamps, we get to the following code: {noformat} else if ((naType0->getTypeQualifier() == NA_DATETIME_TYPE) && (naType1->getTypeQualifier() == NA_DATETIME_TYPE) && (getOperatorType() == ITM_MINUS)) { // Column of DATE datatype is internally created as TIMESTAMP(0). // timestamp(0) - date = diff in days // timestamp(0) - timestamp(0) = diff in days // date - date = diff in days {noformat} After some more checks, we cast both timestamp(0) values to a date. Therefore, the result will be the difference in days. That looks wrong to me. I probably need to consult with some other folks before making this change, but it does not look right to me. > Two timestamp minus and cannot cast to interval value > ----------------------------------------------------- > > Key: TRAFODION-2582 > URL: https://issues.apache.org/jira/browse/TRAFODION-2582 > Project: Apache Trafodion > Issue Type: Bug > Components: Build Infrastructure > Affects Versions: 2.1-incubating > Reporter: Yuan Liu > Fix For: any > > > SQL>select cast((timestamp'2017-04-06 11:50:00' - timestamp'2017-04-06 > 11:45:00') as interval second) from (values(1)); > (EXPR) > ---------- > 0.000000 > --- 1 row(s) selected. > SQL>select cast((timestamp'2017-04-06 11:50:00' - timestamp'2017-04-06 > 11:45:00') as interval minute) from (values(1)); > (EXPR) > ------ > 0 > >>select timestamp'2017-04-06 11:50:00' - timestamp'2017-04-03 11:45:00' from > >>dual; > (EXPR) > ------------- > 3 > --- 1 row(s) selected. -- This message was sent by Atlassian JIRA (v6.3.15#6346)