Thanks, guys, for your help. I tried the filtering method and it works
great.
Sincerely,
Pete
On Sat, Aug 27, 2016 at 12:36 AM, Jonathan Haddad <j...@jonhaddad.com> wrote:
> Ah, i see what you're looking for. No, my schema wouldn't work for that.
> I had read through your question a little quickly.
>
> In cassandra 3.5 support was added for more flexible ALLOW FILTERING
> statements. Here's an example:
>
> CREATE TABLE mytable (
> sensorname text,
> date date,
> time time,
> data int,
> PRIMARY KEY (sensorname, date, time)
> );
>
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-03', '08:00:00', 1);
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-04', '13:00:00', 2);
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-05', '17:00:00', 3);
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-06', '12:00:00', 4);
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-07', '07:00:00', 5);
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-08', '15:00:00', 6);
> cqlsh:test>
> cqlsh:test>
> cqlsh:test> select * from mytable where sensorname = 'test'
> ... and date >= '2016-03-04'
> ... and date <= '2016-03-07'
> ... and time >= '11:00:00'
> ... and time <= '14:00:00'
> ... allow filtering;
>
> sensorname | date | time | data
> ------------+------------+--------------------+------
> test | 2016-03-04 | 13:00:00.000000000 | 2
> test | 2016-03-06 | 12:00:00.000000000 | 4
>
> (2 rows)
>
>
>
> On Fri, Aug 26, 2016 at 10:02 PM Peter Figliozzi <pete.figlio...@gmail.com>
> wrote:
>
>> I don't believe that would let me query a time of day range, over a date
>> range, would it? For example, between 8am and 9am, August 1st through
>> August 10th.
>>
>> On Fri, Aug 26, 2016 at 11:52 PM, Jonathan Haddad <j...@jonhaddad.com>
>> wrote:
>>
>>> Use a timestamp instead of 2 separate fields and you can query on the
>>> range.
>>>
>>> CREATE TABLE mytable (
>>> sensorname text,
>>> reading_time timestamp,
>>> data MAP<text, int>,
>>> PRIMARY KEY (sensorname, reading_time)
>>> );
>>>
>>>
>>>
>>> On Fri, Aug 26, 2016 at 8:17 PM Peter Figliozzi <
>>> pete.figlio...@gmail.com> wrote:
>>>
>>>> I have data from many sensors as time-series:
>>>>
>>>> - Sensor name
>>>> - Date
>>>> - Time
>>>> - value
>>>>
>>>> I want to query windows of both date and time. For example, 8am - 9am
>>>> from Aug. 1st to Aug 10th.
>>>>
>>>> Here's what I did:
>>>>
>>>> CREATE TABLE mykeyspace.mytable (
>>>> sensorname text,
>>>> date date,
>>>> time time,
>>>> data MAP<text, int>,
>>>> PRIMARY KEY (sensorname, date, time)
>>>> );
>>>>
>>>>
>>>> However, when we query this, Cassandra restricts us to an "equal"
>>>> relation for the date, if we are to select a window of time. So with that
>>>> schema, I'd have to query once for each date.
>>>>
>>>>
>>>> What's the right way to do this?? ("Right" defined as extracting a
>>>> window of date and of time in one query.)
>>>>
>>>>
>>>> Thank you,
>>>>
>>>>
>>>> Pete
>>>>
>>>
>>
