Thanks Julian; I added the time unit, there is no SQL syntax error anymore; Although the diff values are all "+0", it should be Kylin's issue;
Uma, please try the query, if you see the same result, then pls open a JIRA to Kylin: http://issues.apache.org/jira/secure/Dashboard.jspa Thanks; 2016-06-12 8:18 GMT+08:00 Julian Hyde <[email protected]>: > You need to include a time unit, e.g. DAY or MONTH or YEAR TO MONTH. The > syntax is > > (datetime - datetime) timeunit > > e.g. > > (d2 - d1) DAY > (d2 - d1) YEAR TO MONTH > > This has been in Calcite for quite a few releases. > > > > > On Jun 11, 2016, at 3:27 AM, ShaoFeng Shi <[email protected]> > wrote: > > > > Julian, from which version Calcite supports the minus operation between > two > > dates? > > > > I tried "select cal_dt, (current_date - cal_dt) as diff from > kylin_cal_dt" > > in Kylin, but get this error: > > > > Error while executing SQL "select cal_dt, (current_date - cal_dt) as diff > > from kylin_cal_dt LIMIT 50000": From line 1, column 17 to line 1, column > > 37: Cannot apply '-' to arguments of type '<DATE> - <DATE>'. Supported > > form(s): '<NUMERIC> - <NUMERIC>' '<DATETIME_INTERVAL> - > > <DATETIME_INTERVAL>' '<DATETIME> - <DATETIME_INTERVAL>' > > > > Kylin uses Calcite 1.6.0 now; Is an upgrade or type cast needed? Thanks! > > > > 2016-06-11 6:02 GMT+08:00 Julian Hyde <[email protected]>: > > > >> In Calcite you can do > >> > >> (currentdate - cal_date) DAYS > >> > >> which returns an INTERVAL DAYS value. > >> > >> In https://issues.apache.org/jira/browse/CALCITE-1124 we added > >> TIMESTAMPADD, TIMESTAMPDIFF. > >> > >> DATEDIFF is mentioned in > >> https://issues.apache.org/jira/browse/CALCITE-759 but that has not > >> been implemented. > >> > >> Julian > >> > >> > >> On Fri, Jun 10, 2016 at 7:54 AM, ShaoFeng Shi <[email protected]> > >> wrote: > >>> I don't see such a function in Calcite (Kylin's SQL parser): > >>> https://calcite.apache.org/docs/reference.html#datetime-functions > >>> > >>> > >>> 2016-06-10 10:04 GMT+08:00 Uma Maheshwar Kamuni <[email protected] > >: > >>> > >>>> i want make DateDiff between currentdate and cal_date (which contains > >> date) > >>>> > >>>> ________________________________ > >>>> From: ShaoFeng Shi <[email protected]> > >>>> Sent: Thursday, June 9, 2016 8:22:08 PM > >>>> To: [email protected] > >>>> Subject: Re: How to do DateDiff in Kylin > >>>> > >>>> Hi Uma, could you please give a sample SQL with DateDiff? > >>>> > >>>> 2016-06-09 13:49 GMT+08:00 Uma Maheshwar Kamuni <[email protected] > >: > >>>> > >>>>> is there any function for DateDiff in Kylin. > >>>>> > >>>> > >>>> > >>>> > >>>> -- > >>>> Best regards, > >>>> > >>>> Shaofeng Shi > >>>> > >>> > >>> > >>> > >>> -- > >>> Best regards, > >>> > >>> Shaofeng Shi > >> > > > > > > > > -- > > Best regards, > > > > Shaofeng Shi > > -- Best regards, Shaofeng Shi
