CODEWITHSUNDEEP
cassandracql
Dan T.
Dan T.

Reputation: 425

Cassandra CQL - get records since 1 before timestamp

I have the following table:

CREATE TABLE records( device_id text, read_time timestamp, data_row text, PRIMARY KEY (device_id, read_time )) WITH CLUSTERING ORDER BY (read_time DESC);

I want to get all the records starting with the one before a specific read_time. Is there a way to do that? I thought maybe having another field previous_read_time, but it will be hard to maintain since I sometime get out of order reads.

Upvotes: 0

Views: 334

Answers (1)

phonaputer
phonaputer

Reputation: 1530

I don't think there's any CQL statement that does this (filter on a timestamp column PLUS the first record not matching the time filter). But depending on your exact case maybe something like the following would work for you?

For example I will find all records with read_time after 2020-05-14 00:00:01 plus the first one on-or-before 2020-05-14 00:00:01:

  1. Select all records after my chosen time (2020-05-14 00:00:01).
SELECT * FROM records WHERE device_id=? AND read_time > '2020-05-14 00:00:01';
  1. From the results of the first query, take the record with the read time closest to 2020-05-14 00:00:01.
// Let's say you find records with the following times. 
// The earliest (closest to the filter's time) is 2020-05-14 00:00:55
2020-05-14 00:00:55
2020-05-14 00:00:56
2020-05-14 00:30:55
2020-05-14 13:30:55
  1. Select again and find the first record which comes before the "closest time to the filter time" you found in step 2:
SELECT * FROM records WHERE device_id=? AND read_time < 2020-05-14 00:00:55 LIMIT 1;

Upvotes: 1

Related Questions