cassandracql

How to filter Cassandra result based on WRITETIME


I would like to get values, whose WRITETIME value is newer than a certain time. I tried this query, but it fails:

SELECT zoom,idx FROM tiles
WHERE zoom=5 AND writetime(tile) > maxTimeuuid('2015-01-01 00:05+0000')
ALLOW FILTERING;

I get this error:

SyntaxException: <ErrorMessage code=2000 [Syntax error in CQL query] 
    message="line 1:68 no viable alternative at input '(' (...and idx > 0 
    and [writetime](...)">

For this table:

CREATE TABLE tiles (
    zoom int,
    idx int,
    tile blob,
    PRIMARY KEY (zoom, idx)
) WITH COMPACT STORAGE

Solution

  • WRITETIME is a function used for displaying the time a specific column was written. It is not a part of the PRIMARY KEY, nor is it indexed, so it cannot be used in your WHERE clause. To be able to query by the time a particular row (not column) was written, you should add that to your table as an additional column and as your first clustering key:

    CREATE TABLE tilesByLastWritten (
        zoom int,
        idx int,
        tile blob,
        lastwritten timeuuid,
        PRIMARY KEY (zoom, lastwritten, idx)
    ) WITH CLUSTERING ORDER BY (lastwritten DESC, idx ASC);
    

    Now this query will work:

    aploetz@cqlsh:stackoverflow2> SELECT * FROM tilesByLastWritten 
        WHERE zoom=5 AND lastwritten > mintimeuuid('2015-07-02 08:30:00-0500');
    
     zoom | lastwritten                          | idx | tile
    ------+--------------------------------------+-----+------
        5 | 3a439c60-20bf-11e5-b9cb-21b264d4c94d |   1 | null
    
    (1 rows)
    

    Notes:

    For more help in this area, give Patrick McFadin's Getting Started With Timeseries Data Modeling a read.