REQL Date and time queries

Question

My documents contain the following fields:

"valid_from":  "Mar 17 08:55:04 2011 GMT"
"valid_to":  "Mar 16 08:55:04 2017 GMT"

My base query currently looks like this:

r.db('items').table('token')

1. How can I query to only retrieve/filter the documents between the current date-time (now) and the "valid_to" field? i.e. anything with a "valid_to" date that is less than the current date/time is considered expired.

2. Calculate the number of days between the "valid_from" and "valid_to" fields. i.e. Determine the validity period in days.

Answer 1

#1

So, basically you want all documents that still haven't expired? (valid_to) is in the future?

In order to do that, you should definitely use RethinkDB time/date functions. In order to do though, you have to create a time instance with either r.time or r. ISO8601. An example of this is:

r.table('tokens')
  .insert({
    "valid_from": r.ISO8601("2011-03-17T08:55:04-07:00"),
    "valid_to": r.ISO8601("2017-03-17T08:55:04-07:00")
  })

Once you've inserted/update the documents using these functions, you can query them using RethinkDB's time/date functions. I you want all documents where valid_to is in the future, you can do this:

r.table('tokens')
  .filter(r.row['valid_to'] > r.now())

If you want to get all documents where valid_to is in the future and valid_from is in the past, you can do this:

r.table('tokens')
  .filter(r.row['valid_to'] > r.now())
  .filter(r.row['valid_from'] < r.now())

#2

If both these properties (valid_to and valid_from) are time/date objects, you can add a days property to each document by doing the following:

r.table('tokens')
  .merge({
    // Subtract the two properties. Divide by seconds * minutes * hours
    "days": (r.row['valid_to'] - r.row['valid_from']) / (60 * 60 * 24)
  })

The Python Code

import rethinkdb as r
conn = r.connect()
cursor = r.table('30715780').filter(r.row['valid_to'] < r.now()).count().run(conn)
print list(cursor)