Flip the problem over. Instead of storing validTo and validFrom, simply store a valid field and partition by (start, end). This may sound wasteful, but disk is cheap:
CREATE TABLE routes ( start text, end text, valid timestamp, PRIMARY KEY ((start, end), valid) ); Now, you can execute something like: SELECT * from routes where start = 'New York' and end = 'Washington' and valid <= 2016-01-31 and valid >= 2016-01-01. On Wed, Mar 23, 2016 at 5:08 PM, Chris Martin <ch...@cmartinit.co.uk> wrote: > Hi all, > > I have a table that represents a train timetable and looks a bit like this: > > CREATE TABLE routes ( > start text, > end text, > validFrom timestamp, > validTo timestamp, > PRIMARY KEY (start, end, validFrom, validTo) > ); > > In this case validFrom is the date that the route becomes valid and > validTo is the date that the route that stops becoming valid. > > If this was SQL I could write a query to find all valid routes between New > York and Washington from Jan 1st 2016 to Jan 31st 2016 using something like: > > SELECT * from routes where start = 'New York' and end = 'Washington' and > validFrom <= 2016-01-31 and validTo >= 2016-01-01. > > As far as I can tell such a query is impossible with CQL and my current > table structure. I'm considering running a query like: > > SELECT * from routes where start = 'New York' and end = 'Washington' and > validFrom <= 2016-01-31 > > And then filtering the rest of the data app side. This doesn't seem ideal > though as I'm going to end up fetching much more data (probably around an > order of magnitude more) from Cassandra than I really want. > > Is there a better way to model the data? > > thanks, > > Chris > > > > -- Vidur Malik [image: ShopKeep] <http://www.shopkeep.com> 800.820.9814 <8008209814> [image: ShopKeep] <https://www.facebook.com/ShopKeepPOS> [image: ShopKeep] <https://twitter.com/shopkeep> [image: ShopKeep] <https://instagram.com/shopkeep/>
