title: Cassandra Data modelling : Timestamp as partition keys
body: <p>I need to be able to return all users that performed an action during a specified interval. The table definition in Cassandra is just below:</p>
  
  <pre><code>create table t ( timestamp from, timestamp to, user text, PRIMARY KEY((from,to), user))
  </code></pre>
  
  <p>I'm trying to implement the following query in Cassandra:</p>
  
  <pre><code>select * from t WHERE from &gt; :startInterval and to &lt; :toInterval
  </code></pre>
  
  <p>However, this query will obviously not work because it represents a range query on the partition key, forcing Cassandra to search all nodes in the cluster, defeating its purpose as an efficient database.</p>
  
  <p>Is there an efficient to model this query in Cassandra?</p>
  
  <p>My solution would be to split both timestamps into their corresponding years and months and use those as the partition key. The table would look like this:</p>
  
  <pre><code> create table t_updated ( yearFrom int, monthFrom int,yearTo int,monthTo int, timestamp from, timestamp to, user text, PRIMARY KEY((yearFrom,monthFrom,yearTo,monthTo), user) )
  </code></pre>
  
  <p>If i wanted the users that performed the action between Jan 2017 and July 2017 the query would look like the following:</p>
  
  <pre><code>select user from t_updated where yearFrom IN (2017) and monthFrom IN (1,2,3,4,5,6,7) and yearTo IN (2017) and  monthTo IN (1,2,3,4,5,6,7)
  </code></pre>
  
  <p>Would there be a better way to model this query in Cassandra? How would you approach this issue?</p>