You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Radu Tudoran <ra...@huawei.com> on 2016/11/03 13:57:19 UTC

question about TIME/TIMESTAMP convertion

Hi,

I am trying to do some simple mathematic operations on a field of type TIMESTAMP and then use this to do the partitioning.
Basically I would like to have the a window defined like:

WINDOW prWin AS ( PARTITION BY FLOOR(current_timestamp  - ETIME)"
                   + " ORDER BY ETIME RANGE INTERVAL '10' MINUTE PRECEDING

The problem is that I cannot do any mathematic operations on the fields of type TIME, TIMESTAMP or DATE. I also tried to convert the field into various other forms: NUMERIC, DECIMAL, BIGINT, INTERVAL ....but none have worked. Can you please help me to figure out how I can solve this.


Re: question about TIME/TIMESTAMP convertion

Posted by Julian Hyde <jh...@apache.org>.
The syntax for date subtractions is "(datetime - datetime) timeUnit [
TO timeUnit ]". So, try

  (CAST(ETIME AS DATE) - DATE '1970-01-01') DAY

Julian



On Fri, Nov 4, 2016 at 3:21 AM, Radu Tudoran <ra...@huawei.com> wrote:
> Hi,
>
> Thank you for the pointers. I tried to work with DATES but the error is pretty much the same, which is that - does not support DATE
>
>  WINDOW prWin AS ( PARTITION BY CAST( (CAST(ETIME AS DATE) - (DATE '1970-01-01')) AS NUMERIC)"
>                    + " ORDER BY ETIME RANGE INTERVAL '10' MINUTE PRECEDING
>
>
> Exception in thread "main" org.apache.calcite.tools.ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 1, column 245 to line 1, column 284: Cannot apply '-' to arguments of type '<DATE> - <DATE>'. Supported form(s): '<NUMERIC> - <NUMERIC>'
> '<DATETIME_INTERVAL> - <DATETIME_INTERVAL>'
> '<DATETIME> - <DATETIME_INTERVAL>'
>         at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:181)
>         at org.huawei.demo.sqlparsercsv.LocalParser3.main(LocalParser3.java:92)
>
> Any thoughts on bypassing this?
>
>
> -----Original Message-----
> From: Julian Hyde [mailto:jhyde@apache.org]
> Sent: Thursday, November 03, 2016 8:32 PM
> To: dev@calcite.apache.org
> Subject: Re: question about TIME/TIMESTAMP convertion
>
> It doesn’t make sense to add two dates together (and please don’t assume they they are integer offsets from 1970-01-01) but it does make sense to subtract them. If you subtract them, you get an interval. You can add intervals to dates, subtract intervals from dates, add intervals to each other, multiply and divide intervals by numbers.
>
> If you want to convert a date to a number, first subtract DATE ‘1970-01-01’, to yield an interval, then cast that interval to a number.
>
> But if possible, stop thinking of dates as numbers. That is really just an implementation detail.
>
> Julian
>
>
>> On Nov 3, 2016, at 6:57 AM, Radu Tudoran <ra...@huawei.com> wrote:
>>
>> Hi,
>>
>> I am trying to do some simple mathematic operations on a field of type TIMESTAMP and then use this to do the partitioning.
>> Basically I would like to have the a window defined like:
>>
>> WINDOW prWin AS ( PARTITION BY FLOOR(current_timestamp  - ETIME)"
>>                   + " ORDER BY ETIME RANGE INTERVAL '10' MINUTE PRECEDING
>>
>> The problem is that I cannot do any mathematic operations on the fields of type TIME, TIMESTAMP or DATE. I also tried to convert the field into various other forms: NUMERIC, DECIMAL, BIGINT, INTERVAL ....but none have worked. Can you please help me to figure out how I can solve this.
>>
>

RE: question about TIME/TIMESTAMP convertion

Posted by Radu Tudoran <ra...@huawei.com>.
Hi,

Thank you for the pointers. I tried to work with DATES but the error is pretty much the same, which is that - does not support DATE 

 WINDOW prWin AS ( PARTITION BY CAST( (CAST(ETIME AS DATE) - (DATE '1970-01-01')) AS NUMERIC)"
                   + " ORDER BY ETIME RANGE INTERVAL '10' MINUTE PRECEDING


Exception in thread "main" org.apache.calcite.tools.ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 1, column 245 to line 1, column 284: Cannot apply '-' to arguments of type '<DATE> - <DATE>'. Supported form(s): '<NUMERIC> - <NUMERIC>'
'<DATETIME_INTERVAL> - <DATETIME_INTERVAL>'
'<DATETIME> - <DATETIME_INTERVAL>'
	at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:181)
	at org.huawei.demo.sqlparsercsv.LocalParser3.main(LocalParser3.java:92)

Any thoughts on bypassing this?


-----Original Message-----
From: Julian Hyde [mailto:jhyde@apache.org] 
Sent: Thursday, November 03, 2016 8:32 PM
To: dev@calcite.apache.org
Subject: Re: question about TIME/TIMESTAMP convertion

It doesn’t make sense to add two dates together (and please don’t assume they they are integer offsets from 1970-01-01) but it does make sense to subtract them. If you subtract them, you get an interval. You can add intervals to dates, subtract intervals from dates, add intervals to each other, multiply and divide intervals by numbers.

If you want to convert a date to a number, first subtract DATE ‘1970-01-01’, to yield an interval, then cast that interval to a number.

But if possible, stop thinking of dates as numbers. That is really just an implementation detail.

Julian


> On Nov 3, 2016, at 6:57 AM, Radu Tudoran <ra...@huawei.com> wrote:
> 
> Hi,
> 
> I am trying to do some simple mathematic operations on a field of type TIMESTAMP and then use this to do the partitioning.
> Basically I would like to have the a window defined like:
> 
> WINDOW prWin AS ( PARTITION BY FLOOR(current_timestamp  - ETIME)"
>                   + " ORDER BY ETIME RANGE INTERVAL '10' MINUTE PRECEDING
> 
> The problem is that I cannot do any mathematic operations on the fields of type TIME, TIMESTAMP or DATE. I also tried to convert the field into various other forms: NUMERIC, DECIMAL, BIGINT, INTERVAL ....but none have worked. Can you please help me to figure out how I can solve this.
> 


Re: question about TIME/TIMESTAMP convertion

Posted by Julian Hyde <jh...@apache.org>.
It doesn’t make sense to add two dates together (and please don’t assume they they are integer offsets from 1970-01-01) but it does make sense to subtract them. If you subtract them, you get an interval. You can add intervals to dates, subtract intervals from dates, add intervals to each other, multiply and divide intervals by numbers.

If you want to convert a date to a number, first subtract DATE ‘1970-01-01’, to yield an interval, then cast that interval to a number.

But if possible, stop thinking of dates as numbers. That is really just an implementation detail.

Julian


> On Nov 3, 2016, at 6:57 AM, Radu Tudoran <ra...@huawei.com> wrote:
> 
> Hi,
> 
> I am trying to do some simple mathematic operations on a field of type TIMESTAMP and then use this to do the partitioning.
> Basically I would like to have the a window defined like:
> 
> WINDOW prWin AS ( PARTITION BY FLOOR(current_timestamp  - ETIME)"
>                   + " ORDER BY ETIME RANGE INTERVAL '10' MINUTE PRECEDING
> 
> The problem is that I cannot do any mathematic operations on the fields of type TIME, TIMESTAMP or DATE. I also tried to convert the field into various other forms: NUMERIC, DECIMAL, BIGINT, INTERVAL ....but none have worked. Can you please help me to figure out how I can solve this.
>