Timestamp comparision not working properly for presto cassandra

0
votes

I have the below presto query to retrieve data from cassandra

select * from table where created_time >= '2017-03-23 00:00:00.0' and created_time <= '2017-03-25 00:00:00.0';

The table has 70 rows with created_time as 2017-03-25 but the above query is not returning me all the rows.

created_time is defined as timeuuid in cassandra. Can someone help me why the query does not work

1
mysqlcassandrapresto
what is your table structure ? – Ashraful Islam
Who in mysql related to here? – David דודו Markovitz
If you do select * from table limit 10;, what values do you get in create_time? Keep in mind that in Presto, the timeuuid type is mapped to VARCHAR. – Christina Wallin

1 Answers

0
votes

If created_time is defined as timeuuid, I would think that a query predicate would compare that to a timeuuid value, not a string or a date string.

We can use the minTimeuuid and maxTimeuuid functions to generate values we can compare to a timeuuid column.

If we want rows with created_time sometime on March 25, we probably want the comparison to be "less than" the 26th '2017-03-26'

For example:

   where created_time >= minTimeuuid('2017-03-25 00:00+0000') 
     and created_time <  minTimeuuid('2017-03-26 00:00+0000')