Thanx guys, it was the timezone thingi … Adding +0000 did the trick select lastname,firstname,dateofbirth from playground.individual where dateofbirth < '2001-01-01T10:00:00' and dateofbirth > '2000-11-18 17:55:17+0000';
-Tobias From: DuyHai Doan <doanduy...@gmail.com> Date: Monday, 19 June 2017 at 17:44 To: Hannu Kröger <hkro...@gmail.com> Cc: "user@cassandra.apache.org" <user@cassandra.apache.org>, Tobias Eriksson <tobias.eriks...@qvantel.com> Subject: Re: SASI index on datetime column does not filter on minutes The +0000 in the date format is necessary to specify timezone On Mon, Jun 19, 2017 at 5:38 PM, Hannu Kröger <hkro...@gmail.com<mailto:hkro...@gmail.com>> wrote: Hello, I tried the same thing with 3.10 which I happened to have at hand and that seems to work. cqlsh:test> select lastname,firstname,dateofbirth from individuals where dateofbirth < '2001-01-01T10:00:00' and dateofbirth > '2000-11-18 17:59:18'; lastname | firstname | dateofbirth ----------+-----------+--------------------------------- Jimmie2 | Lundin | 2000-12-19 17:55:17.000000+0000 Jimmie3 | Lundin | 2000-11-18 17:55:18.000000+0000 Jimmie | Lundin | 2000-11-18 17:55:17.000000+0000 (3 rows) cqlsh:test> select lastname,firstname,dateofbirth from individuals where dateofbirth < '2001-01-01T10:00:00+0000' and dateofbirth > '2000-11-18T17:59:18+0000'; lastname | firstname | dateofbirth ----------+-----------+--------------------------------- Jimmie2 | Lundin | 2000-12-19 17:55:17.000000+0000 (1 rows) cqlsh:test> Maybe you have timezone issue? Best Regards, Hannu On 19 June 2017 at 17:09:10, Tobias Eriksson (tobias.eriks...@qvantel.com<mailto:tobias.eriks...@qvantel.com>) wrote: Hi I have a table like this (Cassandra 3.5) Table id uuid, lastname text, firstname text, address_id uuid, dateofbirth timestamp, PRIMARY KEY (id, lastname, firstname) And a SASI index like this create custom index indv_birth ON playground.individual(dateofbirth) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'SPARSE'}; The data lastname | firstname | dateofbirth ----------+-----------+--------------------------------- Lundin | Jimmie | 2000-11-18 17:55:17.000000+0000 Jansson | Karolin | 2000-12-19 17:55:17.000000+0000 Öberg | Louisa | 2000-11-18 17:55:18.000000+0000 Now if I do this select lastname,firstname,dateofbirth from playground.individual where dateofbirth < '2001-01-01T10:00:00' and dateofbirth > '2000-11-18 17:59:18'; I should only get ONE row, right lastname | firstname | dateofbirth ----------+-----------+--------------------------------- Jansson | Karolin | 2000-12-19 17:55:17.000000+0000 But instead I get all 3 rows !!! Why is that ? -Tobias
