Geek Answers Handbook

Select timeuuid columns corresponding to a specific date

Short version: is it possible to query all timeuuid columns matching a specific date?

More details:

I have a table defined as follows:

CREATE TABLE timetest( key uuid, activation_time timeuuid, value text, PRIMARY KEY(key,activation_time) );

I filled it with one line, as follows ( f0532ef0-2a15-11e3-b292-51843b245f21 is the timeuuid corresponding to the date 2013-09-30 22:19:06+0100 ):

 insert into timetest (key, activation_time, value) VALUES (7daecb80-29b0-11e3-92ec-e291eb9d325e, f0532ef0-2a15-11e3-b292-51843b245f21, 'some value');

And I can query this line as follows:

 select activation_time,dateof(activation_time) from timetest where key=7daecb80-29b0-11e3-92ec-e291eb9d325e

which leads to the following (using cqlsh)

  activation_time | dateof(activation_time) --------------------------------------+-------------------------- f0532ef0-2a15-11e3-b292-51843b245f21 | 2013-09-30 22:19:06+0100

Now let's assume that there is a lot of data in my table, and I want to get all the rows where activation_time corresponds to a certain date, for example 2013-09-30 22:19:06+0100 .

I would expect to be able to query the range of all timeuuids between minTimeuuid('2013-09-30 22:19:06+0100') and maxTimeuuid('2013-09-30 22:19:06+0100') , but this is not seems possible (the following query returns zero rows):

 select * from timetest where key=7daecb80-29b0-11e3-92ec-e291eb9d325e and activation_time>minTimeuuid('2013-09-30 22:19:06+0100') and activation_time<=maxTimeuuid('2013-09-30 22:19:06+0100');

It seems I need to use a hack in which I increment the second date in my request (by a second) in order to catch the rows (rows), i.e.

 select * from timetest where key=7daecb80-29b0-11e3-92ec-e291eb9d325e and activation_time>minTimeuuid('2013-09-30 22:19:06+0100') and activation_time<=maxTimeuuid('2013-09-30 22:19:07+0100');

This seems wrong. Am I missing something? Is there a cleaner way to do this?

The CQL documentation discusses timeuuid functions , but it is quite short in gte / lte expressions with timeuuids, outside of:

The min / maxTimeuuid example selects all rows where the timeuuid, t column is strictly later than 2013-01-01 00: 05 + 0000, but strictly before 2013-02-02 10: 00 + 0000. T> = maxTimeuuid ('2013-01 -01 00: 05 + 0000 ') does not select the timeuuid generated exactly on 2013-01-01 00: 05 + 0000 and is essentially equivalent to t> maxTimeuuid (' 2013-01 -01 00: 05 + 0000 ').

ps the following query also returns null strings:

 select * from timetest where key=7daecb80-29b0-11e3-92ec-e291eb9d325e and activation_time<=maxTimeuuid('2013-09-30 22:19:06+0100');

and the following query returns the string (s):

 select * from timetest where key=7daecb80-29b0-11e3-92ec-e291eb9d325e and activation_time>minTimeuuid('2013-09-30 22:19:06+0100');
+7
cassandra cql
source share
2 answers

I'm sure the problem is that cqlsh does not display milliseconds for your timestamps. So, the real timestamp is like "2013-09-30 22: 19: 06.123 + 0100" when you call maxTimeuuid ('2013-09-30 22 : 19: 06 + 0100 '), since there are no milliseconds, it is considered zero, so it matches the call to maxTimeuuid (' 2013-09-30 22: 19: 06.000 + 0100 ")

And like 22: 19: 06.123> 22: 19: 06.000, which leads to filtering the record.

+8
source share

Not directly related to the answer, but as an additional addon for @dimas answer.
cqlsh (version 5.0.1) seems to now show milliseconds

  system.dateof(id) --------------------------------- 2016-06-03 02:42:09.990000+0000 2016-05-28 17:07:30.244000+0000
0
source share

More articles:


All Articles