Re: SASI index on datetime column does not filter on minutes

[Hannu Kröger]

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)
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