Cassandra - get interval contains given timestamp

Question

I have table in Cassandra:

CREATE TABLE test (
 bucket int,  
 start timestamp,
 end timestamp,  
 PRIMARY KEY((bucket),start, end)
);

And I would like to get a query like that:

SELECT * FROM test where bucket = 1 and start <= current_time and end >= current_time

In another word, I would like to find interval which contains given timestamp.

I know that query is wrong. I tried to do it also with Multi-column slice restrictions, but it's also useless in this case. Is there some way to do this?

Please take a look at this Link: https://stackoverflow.com/questions/24968823/how-to-construct-range-query-in-cassandra — cdatta, Feb 07 '18 at 21:19
This is about filtering by one column. I have two columns to filter and there is a problem. — Adam Sowinski, Feb 09 '18 at 06:56

score 2 · Answer 1 · answered Feb 07 '18 at 20:56

I have worked a similar problem in the past, too. The way I did it, was to use a column to track start/end. Then have one row for the start of an event and one row for the end. Let's try creating a table to store "Nerd Holidays," like this:

CREATE TABLE nerd_holidays (
  month_bucket int,  
  event_time timestamp,
  beginend text,  
  name text,
  PRIMARY KEY ((month_bucket), event_time, beginend)
) WITH CLUSTERING ORDER BY (event_time DESC, beginend ASC);

And I'll insert some rows:

INSERT INTO nerd_holidays (month_bucket, event_time, beginend, name)
VALUES (3,'2018-03-14 00:00:00','begin','Pi Day');
INSERT INTO nerd_holidays (month_bucket, event_time, beginend, name)
VALUES (3,'2018-03-14 23:59:59','end','Pi Day');
INSERT INTO nerd_holidays (month_bucket, event_time, beginend, name)
VALUES (5,'2018-05-04 00:00:00','begin','Star Wars Day');
INSERT INTO nerd_holidays (month_bucket, event_time, beginend, name)
VALUES (5,'2018-05-04 23:59:59','end','Star Wars Day');
INSERT INTO nerd_holidays (month_bucket, event_time, beginend, name)
VALUES (9,'2018-09-19 00:00:00','begin','Talk Like a Pirate Day');
INSERT INTO nerd_holidays (month_bucket, event_time, beginend, name)
VALUES (9,'2018-09-19 23:59:59','end','Talk Like a Pirate Day');
INSERT INTO nerd_holidays (month_bucket, event_time, beginend, name)
VALUES (9,'2018-09-25 00:00:00','begin','Hobbit Day');
INSERT INTO nerd_holidays (month_bucket, event_time, beginend, name)
VALUES (9,'2018-09-25 23:59:59','end','Hobbit Day');

Now I can query data for a specific time in the month of September, like this:

cassdba@cqlsh:stackoverflow> SELECT * FROM nerd_holidays
    WHERE month_bucket=9
    AND event_time >= '2018-09-18 00:00'
    AND event_time <= '2018-09-19 08:33' ;

 month_bucket | event_time                      | beginend | name
--------------+---------------------------------+----------+------------------------
            9 | 2018-09-19 05:00:00.000000+0000 |    begin | Talk Like a Pirate Day

(1 rows)

As you can see, "Talk Like a Pirate Day" begins within the requested date range.

Thanks for your answer but I think it's not a solution for my problem. I need a little different query than you. Take a look once again at my query. If I use your schema I will get all rows from the bucket. — Adam Sowinski, Feb 09 '18 at 06:53

Cassandra - get interval contains given timestamp

1 Answers1