How can we filter rows based on timestamp column?

user_9424040 0
Viewed 281

I have a cassandra column which is of type date and has values in timestamp format like below. How can we filter rows based on this column which have date greater than today's date?

Example:

Type: date Timestamp: 2021-06-29 11:53:52 +00:00 TTL: null Value: 2021-03-16T00:00:00.000+0000

I was able to filter rows using columname <= '2021-09-25' which gives ten rows some of them having dates on sep 23 and 24. When i filter using columname < '2021-09-24', i get an error like below

An error occurred on line 1 (use Ctrl-L to toggle line numbers):
Cassandra failure during read query at consistency ONE (1 responses were required but only 0 replica responded, 1 failed)
cql cassandra
1 Answers

The CQL timestamp data type is encoded as the number of milliseconds since Unix epoch (Jan 1, 1970 00:00 GMT) so you need to be precise when you're working with timestamps.

Depending on where you're running the query, the filter could be translated in the local timezone. Let me illustrate with this example table:

CREATE TABLE community.tstamptbl (
    id int,
    tstamp timestamp,
    PRIMARY KEY (id, tstamp)
)

These 2 statements may appear similar but translate to 2 different entries:

INSERT INTO tstamptbl (id, tstamp) VALUES (5, '2021-08-09');

INSERT INTO tstamptbl (id, tstamp) VALUES (5, '2021-08-09 +0000');

The first statement creates an entry with a timestamp in my local timezone (Melbourne, Australia) while the second statement creates an entry with a timestamp in UTC (+0000):

cqlsh:community> SELECT * FROM tstamptbl WHERE id = 5;

 id | tstamp
----+---------------------------------
  5 | 2021-08-08 14:00:00.000000+0000
  5 | 2021-08-09 00:00:00.000000+0000

Similarly, you need to be precise when reading the data. You need to specify the timezone to remove ambiguity. Here are some examples:

SELECT * FROM tstamptbl WHERE id = 5 AND tstamp < '2021-08-09 +0000';
SELECT * FROM tstamptbl WHERE id = 1 AND tstamp < '2021-08-10 12:00+0000';
SELECT * FROM tstamptbl WHERE id = 1 AND tstamp < '2021-08-10 12:34:56+0000';

In the second part of your question, the error isn't directly related to your filter. The problem is that the replica(s) failed to respond for whatever reason (e.g. unresponsive/overloaded, down, etc). You need to investigate that issue separately. Cheers!

user_42695350
Related