multi-column date range query

[Ram]

Hi,

I have recently started exploring Redis DB and loving it for its small
memory footprint and ease of use.

I have a use cases which stores multi-column data and I am not sure if
Redis is the right DB for this task, so just wanted to get some advice
from the board.

The use case that I have is to store following multi-column row:

{ Zodiac: 'Leo', dateStart: '07/23', dateEnd: '08/23'}

My query would take a date as input say currentDate= '07/29'
and search for this date in two columns such that:
dateStart <= currentDate <=dateEnd
and return Zodiac.

I have approx 5 million such rows on which i need to search. I
Insertion would be one time task, i would mainly be using this for
querying the data. I am familiar with both mongoDB and solr but would
really like to try Redis if possible.

My question is how to go about storing such data and searching if
Redis DB is the right tool for such use case.

Appreciate your help.

-Ram

[Dvir Volk]

Hey Ram,

What you want is certainly doable in redis, using the sorted set feature - ZSET.

let's say you have all the zodiac signs saved in HASH objects, each with a key like sign:leo, sign:pices etc, and members start_date, end_date, etc

then you take the start date and end date, and convet them to a number, the simplest idea I have is just the day of year.

so leo is (making this up) days 113-144 

then you index those in a sorted set. you enter the end end DOY of each sign to sorted set (there's no need to actually input the start date for this exact use case, but you can use either)

ZADD date_index 144 leo

for all signs.

now when you want to know what sign a date is in, you convert it to day-of-year as well,

for example day 128

now you do

ZRANGEBYSCORE date_index 128 +inf LIMIT 0 1

this will give you the nearest end of month record, in this case "leo"

then you can go and fetch the HASH containing the data, i.e HGETAL sign:leo

this is very fast, I'm using this method for example to resolve an ip adress to an ip range for location resolving.

of course you can use this with any numerical index.

read a bit more about ZSETs here: http://redis.io/commands/zadd

--
You received this message because you are subscribed to the Google Groups "Redis DB" group.
To post to this group, send email to redi...@googlegroups.com.
To unsubscribe from this group, send email to redis-db+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/redis-db?hl=en.

[Ram]

Hi Dvir,
Thanks for the detailed instructions.
I am going to give this a try.

-Ram