select query on cassandra with where clause field as timestamp

Question

I need to run a select query with a where clause with field as timestamp datatype.

In scheme the datatype of SUBMIT date is timestamp

"SUBMIT_DATE" timestamp,

select "SUBMIT_DATE" from "MESSAGES_2016_02_18";

 SUBMIT_DATE
--------------------------
 2016-02-18 16:26:14+0530

(1 rows)

but when i try

select * from "MESSAGES_2016_02_18" where "SUBMIT_DATE"='2016-02-18 16:26:14+0530';

(0 rows)

Can anyone tell me how to get the desired result?

the table is:

CREATE TABLE "MESSAGES_2016_02_18" (
  "ADDR_DST_DIGITS" ascii,
  "ID" uuid,
  "SUBMIT_DATE" timestamp,
  "TARGET_ID" ascii,
  "VALIDITY_PERIOD" timestamp,
  PRIMARY KEY (("ADDR_DST_DIGITS"), "ID")
) WITH
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='';

I created a index as well as "SUBMIT_DATE" is not a primary key

create INDEX fetch_date ON "TelestaxSMSC"."MESSAGES_2016_02_18" ("SUBMIT_DATE");

Please let me know how do i fetch the details with the above query.

CREATE TABLE "MESSAGES_2016_02_18" ("ADDR_DST_DIGITS" ascii,"SUBMIT_DATE" timestamp, "TARGET_ID" ascii, "VALIDITY_PERIOD" timestamp, PRIMARY KEY (("ADDR_DST_DIGITS"), "ID") ) ; — piyushj
I cannot give the whole table as the comment limit doesnt permit it, — piyushj
You can also edit your question and add your create table statement — Will
Did you happen to INSERT your timestamp with milliseconds? If so, this might help you: stackoverflow.com/questions/28547616/… — Aaron

doanduyhai doanduyhai · Accepted Answer · 2016-02-18T12:05:02

Humm, it looks like there is a problem with timezone config. I've seen this issue before.

what is your Cassandra version ?
is the Cassandra server time set on UTC ?
is the server on which you're running cqlsh time set on UTC ?

select query on cassandra with where clause field as timestamp

1 Answers