how to get data based on date range in redis

Question

I have a log data in mysql

id |   value | date
1  |   10.2  | 2017-07-20 18:00:00
2  |   10.5  | 2017-07-20 18:00:01
3  |   10.3  | 2017-07-20 18:00:03

then transformed it into hash dan sorted set in redis. This is my hashes:

hmset mylog:1 id 1 value 10.2 date 1388534400 
hmset mylog:2 id 2 value 10.5 date 1388534401
hmset mylog:3 id 3 value 10.3 date 1388534402

and sorted set :

zadd log_date 1388534400 1
zadd log_date 1388534401 2
zadd log_date 1388534402 3

I want to perform query just like WHERE date beetween .... and ....

Is there any possible way to get data from hashes, based on date range in sorted set?

Thanks!

Solution 1

There are two ways possible.

1. Keep data in hashes, dates as unix timestamp in sorted set, and query the sorted set using ZRANGE to get the ids, then query the hashes with those ids

2. Another approach I would recommend if your MySQL row data is simple i.e. 2-3 columns with primitive values,is to store the data itself as a key in a Sorted set, with date being the score.

zadd log_date 1388534400 1_10.2

The position of the elements on splitting your key is fixed hence [0] index would give you the id,1 index would give you the value. This way all your data would lie in the sorted set, and you can query the data using ZRANGE ( with WITHSCORE flag ) to fetch all the data along with the dates within the provided unix timestamp dates. This approach is memory efficient, and also saves you from the problem of data linking into two points where you would have to add or delete data in sorted set as well as the hash. Here only the sorted set is required.

Solution 2

To do that, first perform the query on the Sorted Set to obtain the members in the date range, and then fetch the relevant Hashes.

Comments

[marman]

I know this is an entirely different question, but in terms of performance is this better than querying the db ? what happens when you have thousands of records ?

[Madbreaks]

This answer could certainly be more complete but it does not deserve a downvote since it's absolutely correct.

[Itamar Haber]

@Madbreaks ty for the kind words

[Mrinal Kamboj]

How about a direct redis search query on a hash attribute which use AND filter on the either end of the unix time stamp stored in the attribute ?