cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Martin <ch...@cmartinit.co.uk>
Subject DataModelling to query date range
Date Wed, 23 Mar 2016 21:08:33 GMT
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

Mime
View raw message