Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

rht.rajput_193125 avatar image
rht.rajput_193125 asked ·

How can I compare with timestamp values in CQL?

For DS201, Clustering column exercise, i am trying to work on "8) Change your query to retrieve videos made in 2013 or later."

I have following table structure:

CREATE TABLE killrvideo.videos_by_tag (
    tag text,
    added_date timestamp,
    title text,
    video_id uuid,
    PRIMARY KEY (tag, added_date)
) WITH CLUSTERING ORDER BY (added_date DESC)

Now when i am trying to fetch record with following query, its not showing anything if i use following query:

select * from videos_by_tag where tag='cassandra' and added_date >2012 and added_date<2014;

However its giving all 3 rows when doing this:

select * from videos_by_tag where tag='cassandra' and added_date >2012;
cqlclustering key
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

bettina.swynnerton avatar image
bettina.swynnerton answered ·

Hi,

in your comparison, you will need to compare against a valid timestamp value:

For example:

select * from videos_by_tag where tag='cassandra' and added_date >= '2013-01-01' and added_date< '2014-01-01';

See here for the reference on the timestamp format in cql:

https://docs.datastax.com/en/cql-oss/3.x/cql/cql_reference/timestamp_type_r.html

With your syntax using an unformatted number, you are comparing against the number of miliseconds elapsed since 1 January 1970.

If you compared for example with 1594044226000 (UNIX timestamp in miliseconds at the time of writing this answer), you would get all rows, as all your records predate this time:

select * from videos_by_tag where tag='cassandra' and added_date < '1594044226000' ;

I hope this helps!

Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.