Thanks, for you answer. Wouldn’t simple `select * from subscriptions where next_billing_date = '2016-10-25’` require full scan of all partitions?
> On 23 Sep 2016, at 14:28, Peter Lin <wool...@gmail.com> wrote: > > > Ignoring noSql for a minute, the standard way of modeling this in car and > health insurance is with effective/expiration day. Commonly called > bi-temporal data modeling. > > How people model bi-temporal models varies quite a bit from first hand > experience, but the common thing is to have transaction timestamp, effective > day and expiration day. This way, when the system needs to get all the > policies that expires in 30 days, it would run a query like this > > select * from policies where expiration = '2016-10-25' > > the batch application is responsible for calculating the expiration by > getting today's date + 30 days. The first thing I would change is modify > next_billing_date from timestamp to date and set the hour to 00:00:00. It > doesn't make sense to use timestamp datatype for this particular use case. > > Getting back to Cassandra and CQL. You should be able to just query it > without having to resort to materialized views and secondary indexes. If you > really want to index it, use Solr or buy a license of DSE. The temporal > database I built on top of Cassandra uses Solr, which gives me greater > flexibility and control over indexes and query performance. > > > On Fri, Sep 23, 2016 at 4:42 AM, Denis Mikhaylov <notxc...@gmail.com> wrote: > Hi! > > I have question regarding data modelling. > > Let’s say that I have `subscriptions` table with two columns `subscription_id > text` and `next_billing_date timestamp`. > > How do I model a table to efficiently query all subscriptions due today > (something like `where next_billing_date <= today`) >
