You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by ShaoFeng Shi <sh...@apache.org> on 2016/06/09 14:52:08 UTC
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 <uk...@centramed.co>:
> is there any function for DateDiff in Kylin.
>
--
Best regards,
Shaofeng Shi
Re: How to do DateDiff in Kylin
Posted by ShaoFeng Shi <sh...@apache.org>.
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 <jh...@apache.org>:
> 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 <sh...@apache.org>
> 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 <jh...@apache.org>:
> >
> >> 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 <sh...@apache.org>
> >> 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 <ukamuni@centramed.co
> >:
> >>>
> >>>> i want make DateDiff between currentdate and cal_date (which contains
> >> date)
> >>>>
> >>>> ________________________________
> >>>> From: ShaoFeng Shi <sh...@apache.org>
> >>>> Sent: Thursday, June 9, 2016 8:22:08 PM
> >>>> To: dev@kylin.apache.org
> >>>> 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 <ukamuni@centramed.co
> >:
> >>>>
> >>>>> is there any function for DateDiff in Kylin.
> >>>>>
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> Best regards,
> >>>>
> >>>> Shaofeng Shi
> >>>>
> >>>
> >>>
> >>>
> >>> --
> >>> Best regards,
> >>>
> >>> Shaofeng Shi
> >>
> >
> >
> >
> > --
> > Best regards,
> >
> > Shaofeng Shi
>
>
--
Best regards,
Shaofeng Shi
Re: How to do DateDiff in Kylin
Posted by Julian Hyde <jh...@apache.org>.
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 <sh...@apache.org> 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 <jh...@apache.org>:
>
>> 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 <sh...@apache.org>
>> 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 <uk...@centramed.co>:
>>>
>>>> i want make DateDiff between currentdate and cal_date (which contains
>> date)
>>>>
>>>> ________________________________
>>>> From: ShaoFeng Shi <sh...@apache.org>
>>>> Sent: Thursday, June 9, 2016 8:22:08 PM
>>>> To: dev@kylin.apache.org
>>>> 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 <uk...@centramed.co>:
>>>>
>>>>> is there any function for DateDiff in Kylin.
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Best regards,
>>>>
>>>> Shaofeng Shi
>>>>
>>>
>>>
>>>
>>> --
>>> Best regards,
>>>
>>> Shaofeng Shi
>>
>
>
>
> --
> Best regards,
>
> Shaofeng Shi
Re: How to do DateDiff in Kylin
Posted by ShaoFeng Shi <sh...@apache.org>.
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 <jh...@apache.org>:
> 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 <sh...@apache.org>
> 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 <uk...@centramed.co>:
> >
> >> i want make DateDiff between currentdate and cal_date (which contains
> date)
> >>
> >> ________________________________
> >> From: ShaoFeng Shi <sh...@apache.org>
> >> Sent: Thursday, June 9, 2016 8:22:08 PM
> >> To: dev@kylin.apache.org
> >> 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 <uk...@centramed.co>:
> >>
> >> > is there any function for DateDiff in Kylin.
> >> >
> >>
> >>
> >>
> >> --
> >> Best regards,
> >>
> >> Shaofeng Shi
> >>
> >
> >
> >
> > --
> > Best regards,
> >
> > Shaofeng Shi
>
--
Best regards,
Shaofeng Shi
Re: How to do DateDiff in Kylin
Posted by Julian Hyde <jh...@apache.org>.
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 <sh...@apache.org> 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 <uk...@centramed.co>:
>
>> i want make DateDiff between currentdate and cal_date (which contains date)
>>
>> ________________________________
>> From: ShaoFeng Shi <sh...@apache.org>
>> Sent: Thursday, June 9, 2016 8:22:08 PM
>> To: dev@kylin.apache.org
>> 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 <uk...@centramed.co>:
>>
>> > is there any function for DateDiff in Kylin.
>> >
>>
>>
>>
>> --
>> Best regards,
>>
>> Shaofeng Shi
>>
>
>
>
> --
> Best regards,
>
> Shaofeng Shi
Re: How to do DateDiff in Kylin
Posted by ShaoFeng Shi <sh...@apache.org>.
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 <uk...@centramed.co>:
> i want make DateDiff between currentdate and cal_date (which contains date)
>
> ________________________________
> From: ShaoFeng Shi <sh...@apache.org>
> Sent: Thursday, June 9, 2016 8:22:08 PM
> To: dev@kylin.apache.org
> 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 <uk...@centramed.co>:
>
> > is there any function for DateDiff in Kylin.
> >
>
>
>
> --
> Best regards,
>
> Shaofeng Shi
>
--
Best regards,
Shaofeng Shi
Re: How to do DateDiff in Kylin
Posted by Uma Maheshwar Kamuni <uk...@centramed.co>.
i want make DateDiff between currentdate and cal_date (which contains date)
________________________________
From: ShaoFeng Shi <sh...@apache.org>
Sent: Thursday, June 9, 2016 8:22:08 PM
To: dev@kylin.apache.org
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 <uk...@centramed.co>:
> is there any function for DateDiff in Kylin.
>
--
Best regards,
Shaofeng Shi