Relative Dates

[ErisDS]

We have a need for relative date handling in NQL.

The exact solution I propose is do the translation from a relative date into an absolute date in the NQL language. This means adding a new syntax to NQL to represent a relative date and then processing that into an exact date inside the parser, and storing an absolute query in our intermediary format (Mongo query JSON). Our intermediary format would then represent a simple absolute date query to perform on the database, which we can already do.

This solution does not add relative date handling all the way to the database / JSON
lookup. The query that is performed will be absolute, but in the URL/API query we will be able to represent this as relative. The reason for this is that neither mongo query/mingo nor knex have support for relative dates.

Proposed Syntax:

last_seen_at:>=now-1d (since yesterday)

last_seen_at:<=now+1d (before tomorrow)

In detail:

>= or <= are NQL’s greater than or less than operators.

now a keyword that represents now

-/+ plus or minus, used to indicate dates since or dates before now

1d represents “one day” - an interval to add or subtract from now

We can support different intervals:

• now-30d (30 days ago)
• now-4w (4 weeks ago)
• now-12M (12 months ago)
• now-1y (1 year ago)
• now-1h (1 hour ago)
• now-1m (1 minute ago)
• now-1s (1 second ago)

Note: M is capitalised for Months as per datetime formatters in most languages, where m represents minutes.

last_seen_at:>=now-1d would become the following Mongo Query JSON:

{
  "last_seen_at": {"$gt": "2022-02-27T10:45:00Z"}
}

[ErisDS]

I think we probably need to support now on its own 🤔

now-0d will work, but it's ugly

The problem is with supporting now on it's own... that will clash with tags called now 🤔