Selecting timeuuid columns corresponding to a specific date

Question

Short version: Is it possible to query for all timeuuid columns corresponding to a particular 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 have populated this with a single row, as follows (f0532ef0-2a15-11e3-b292-51843b245f21 is a 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 for that row as follows:

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

which results in the following (using cqlsh)

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

Now lets assume there's a lot of data in my table and I want to retrieve all rows where activation_time corresponds to a particular date, say 2013-09-30 22:19:06+0100.

I would have expected to be able to query for 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 doesn't seem 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 whereby I increment the second date in my query (by a second) to catch the row(s), 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 feels wrong. Am I missing something? Is there a cleaner way to do this?

The CQL documentation discusses timeuuid functions but it's pretty short on gte/lte expressions with timeuuids, beyond:

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

p.s. the following query also returns zero rows:

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 row(s):

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

dimas dimas · Accepted Answer · 2013-11-21T18:04:57

I'm sure the problem is that cqlsh does not display milliseconds for your timestamps So the real timestamp is something like '2013-09-30 22:19:06.123+0100' When you call maxTimeuuid('2013-09-30 22:19:06+0100') as milliseconds are missing, zero is assumed so it is the same as calling maxTimeuuid('2013-09-30 22:19:06.000+0100')

And as 22:19:06.123 > 22:19:06.000 that causes record to be filtered out.

Selecting timeuuid columns corresponding to a specific date

2 Answers