question

rht.rajput_193125 avatar image
rht.rajput_193125 asked Erick Ramirez edited

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
Toggle Comment visibility. Current Visibility: Viewable by all users

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
Toggle Comment visibility. Current Visibility: Viewable by all users

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