CODEWITHSUNDEEP
cassandracql
Adam Sowinski
Adam Sowinski

Reputation: 31

Cassandra - get interval contains given timestamp

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?

Upvotes: 2

Views: 1394

Answers (1)

Aaron
Aaron

Reputation: 57843

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.

Upvotes: 2

Related Questions