Filtering database entries by Author & between unix timestamp

Question

I've been stuck on trying to find a way to get all database entries from a specific author ID and between 2 timestamps. I've tried reading the documentation and messing around with the ways demonstrated and can't figure it out.

For example: getting all messages in the database from an author with id "281199286765748225" and between Unix timestamps of 1614920000 and 1614924579.

I've created a table within the "testing" database called "messages", here's an example layout:

You don't show how you're trying to get the filtered data, but if you're database accepts something like standard queries, then you should be able to use something similar to SELECT * FROM messages WHERE id = "281199286765748225" AND 'date' BETWEEN 1614920000 AND 1614924579; There may be an issue with the your date column being a reserved word or function name in your database's query language. By quoting the column name you may get around this. If not, try changing the column name to something else, create_date, possibly? — , Mar 05 '21 at 07:02
I don't know rethink, so I'm guessing that you're going to have to limit the data coming out of the database, and then perform your filtering with javascript or whatever scripting/formatting language you are using. Please specify the scripting language. — , Mar 05 '21 at 11:15

score 0 · Answer 1 · answered Mar 06 '21 at 10:10

There are at least two ways. The most simple, without secondary-indexes, by using .filter:

r.db('testing').table('messages')
    .filter({author: '281199286765748225'})
    .filter(r.row('date').ge(1614920000))
    .filter(r.row('date').lt(1614924579))

And by using secondary-indexes, at first create index:

r.db('testing').table('messages')
    .indexCreate('author_date', [r.row("author"), r.row("date")])

then use .between:

r.db('testing').table('messages')
    .between(
        ['281199286765748225', 1614920000],
        ['281199286765748225', 1614924579],
        {index: 'author_date'}
    )

Filtering database entries by Author & between unix timestamp

1 Answers1