Relative Dates

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"}
}

Start by locating the NQL parser and the intermediary Mongo query JSON conversion described in the issue. Define how the proposed now±interval syntax is parsed into an absolute date, including supported units and operators, then verify that the resulting query remains an absolute database lookup.