"Greater than" where-condition on timeuuid using Datastax C# Cassandra Driver

Question

How do I make "greater than" or "less than" where-conditions in CQL queries on the timeuuid data type using the Datastax C# driver?

I have a table in Cassandra for storing cookie history sorted by time stamp as timeuuid:

CREATE TABLE cookie_history (
    cookie_id text,
    create_date timeuuid,
    item_id text,
    PRIMARY KEY ((cookie_id), create_date)
);

The table is mapped using a C# class for querying using the Datastax C# Cassandra driver:

[Table("cookie_history")]
public class CookieHistoryDataEntry
{
    [PartitionKey(1)]
    [Column("cookie_id")]
    public string CookieID;

    [ClusteringKey(1)]
    [Column("create_date")]
    public Guid CreateDate;

    [Column("item_id")]
    public string ItemID;
}

For a given cookie I want all items after a given time stamp.

        var myTimeUuid = new Guid("5812e74d-ba49-11e3-8d27-27303e6a4831");
        var table = session.GetTable<CookieHistoryDataEntry>();
        var query = table.Where(x => x.CookieID == myCookieId
                                  && x.CreateDate > myTimeUuid);

But this (x.CreateDate > myTimeUuid) gives me a compile time error:

Operator '>' cannot be applied to operands of type 'System.Guid' and 'System.Guid'

score 3 · Accepted Answer · answered Apr 04 '14 at 08:09

It is possible to use "greater than" on timeuuid in raw CQL. So one solution is to execute raw CQL from the driver:

session.Execute(@"select * 
    from cookie_history 
    where cookie_id = 1242a96c-4bd4-8505-1bea-803784f80c18 
    and create_date > 5812e74d-ba49-11e3-8d27-27303e6a4831;");

Skrymsli · Answer 2 · 2015-08-27T15:24:44.590

You can use Linq if you use CompareTo():

var query = table.Where(x => x.CookieID == myCookieId &&
                        x.CreateDate.CompareTo(myTimeUuid) > 0;

Here's the code that handles the CompareTo.

Related: If you need to compare partition keys which require use of the Cassandra token() method, you can do that with CqlToken.Create:

var query = table.Where(x => 
    CqlToken.Create(x.PartitionKeyProperty) >= CqlToken.Create(3);

score 0 · Answer 3 · answered Apr 02 '14 at 20:38

0

Is there a reason that you want to attempt to represent your date as a Guid and not an actual date type? There's no concept of greater than or less than with regards to Guid's, unless this is some edge case that I'm not aware of. Date A can be greater than Date B, from everything I've ever seen that's not true of Guid's.

answered Apr 02 '14 at 20:38

Todd Nakamura

170
1
8

I am using the date as my column key. I am using Guid representations of the dates to avoid collision of column keys for co occurring events. To get around this problem of "greater than" not supported, I will query for the newest X entries and the filter on date in memory. – user628904 Apr 03 '14 at 08:56
I think the discussion in this thread is very relevant for your situation: http://stackoverflow.com/questions/5086192/sql-server-using-datetime-as-primary-key – Todd Nakamura Apr 03 '14 at 18:43
The main point in your linked discussion is that we only will have a few collisions of entries for same time stamp when we also consider a user specific key as part of the identifier. But I can not assume this, because some of my entries do not have a "time part" in the time stamp (but only the "date part"), so I need to use timeuuid to avoid conflicts in the column keys. – user628904 Apr 04 '14 at 08:08
1

A guid/uuid is not the same as a timeuuid even though they have the same format. A timeuuid makes guarantees about uniqueness and ordering: http://docs.datastax.com/en/cql/3.3/cql/cql_reference/uuid_type_r.html – Skrymsli Aug 27 '15 at 15:29

score 0 · Answer 4 · answered Jun 20 '16 at 13:45

Actually it is also possible to do with QueryBuilder not just as raw CQL:

select.where(QueryBuilder.eq('cookie_id', cookie_id).  
  and(QueryBuilder.gt("create_date", 
      QueryBuilder.fcall("maxTimeuuid", 
          QueryBuilder.fcall("unixTimestampOf", 
              QueryBuilder.raw(timeuuid_as_string))));

"Greater than" where-condition on timeuuid using Datastax C# Cassandra Driver

4 Answers4