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
>>>
>>
>
