0

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.

piyushj
  • 1,546
  • 5
  • 21
  • 29
  • Can you please give your CREATE TABLE script ? – doanduyhai Feb 18 '16 at 11:15
  • 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 Feb 18 '16 at 11:25
  • I cannot give the whole table as the comment limit doesnt permit it, – piyushj Feb 18 '16 at 11:27
  • You can also edit your question and add your create table statement – Will Feb 18 '16 at 11:29
  • Did you happen to INSERT your timestamp with milliseconds? If so, this might help you: http://stackoverflow.com/questions/28547616/cassandra-cqlsh-how-to-show-microseconds-milliseconds-for-timestamp-columns/28549241#28549241 – Aaron Feb 18 '16 at 13:30
  • cqlsh:TelestaxSMSC> select blobAsBigint(timestampAsBlob("SUBMIT_DATE")) from "MESSAGES_2016_02_18"; blobAsBigint(timestampAsBlob(SUBMIT_DATE)) -------------------------------------------- 1455792974740 1455792974740 =====================================(2 rows)yes i guess its in milliseconds: – piyushj Feb 19 '16 at 04:16
  • but this blobAsBigint(timestampAsBlob) function cant be used in where criteria, correct me if i am wrong.... – piyushj Feb 19 '16 at 04:21
  • okay able to direct comparision with epoch for timestamp datatype, ....but for me main problem occurs when i do ..............................................................cqlsh:TelestaxSMSC> select * from "MESSAGES_2016_02_18" where "SUBMIT_DATE">='1455792974740'; Bad Request: No indexed columns present in by-columns clause with Equal operator here the problem is that submit_date is not a primary key but only a index, how can i run this query? – piyushj Feb 19 '16 at 05:02

1 Answers1

0

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 ?
doanduyhai
  • 8,712
  • 27
  • 26