Search code examples
cassandracql

How do I group by date in Cassandra?

I'm trying to find a query in Cassandra cql to group by date. I have "date" datatype where the date is like: "mm-dd-yyyy". I'm just trying to extract the year and then group by. How to achieve that?

SELECT sum(amount) FROM data WHERE date = 'yyyy'
Solution

  • You cannot do a partial filter with just the year on a column of type date. It is an invalid query in Cassandra.

    The CQL date type is encoded as a 32-bit integer that represents the days since epoch (Jan 1, 1970).

    If you need to filter based on year the you will need to add a column to your table like in this example:

    CREATE TABLE movies (
    movie_title text,
    release_year int,
    ...
    PRIMARY KEY ((movie_title, release_year))
)

    Here's an example for retrieving information about a movie:

    SELECT ... FROM movies WHERE movie_title = ? AND release_year = ?