How to perform date range queries in faunadb

Question

I'm trying to perform a date range query to return all the records between 2 dates, but no matter what I try, it always returns zero results, even though I have the data existing in the collection.

My index was initially defined like this:

CreateIndex({
    name: "entries_by_date_only",
    source: Collection("Entries"),
    terms: [{ field: ["data", "created"] }]
})

This is a sample record from the Entries collection:

{
  "ref": Ref(Collection("Entries"), "36561645689871265"),
  "ts": 1684938186510000,
  "data": {
    "email": "[email protected]",
    "firstEntry": true,
    "state": "NY",
    "optin": true,
    "lang": "en",
    "created": "2023-08-22T10:23:06-05:00",
    "name": "Test Name",
    "ip": "123.123.456.456",
    "eligibility": true
  }
}

And this is the query I am trying:

const START_DATE = req.params.startDate; // I have tried 2023-08-01 AND 2023-08-01T00:00:00-05:00
const END_DATE = req.params.endDate; // I have tried: 2023-08-30 AND 2023-08-30T23:59:59-05:00

const entriesQuery = q.Map(
    q.Paginate(
        q.Range(q.Match(q.Index("entries_by_date_only")), START_DATE, END_DATE)
    ),
    q.Lambda("entry", q.Get(q.Var("entry")))
);

const entriesResult = await client.query(entriesQuery);

console.log("ENTRIES", entriesResult); // Logs: ENTRIES { data: [] }

Is there anything I'm missing here? The field holds the string in ISO format, so I thought I could use it for this purpose. Is that even possible? Thanks in advance for any help.

Answer 1

The issue seems to be with the way you are constructing the date range query. The Range function in FaunaDB expects the range values to be of type Date, but you are passing them as strings. To fix this, you need to convert the start and end dates to Date type before passing them to the Range function.

To convert a string to a Date type in FaunaDB, you can use the Date function.

Check this out:

const START_DATE = q.Date(req.params.startDate);
const END_DATE = q.Date(req.params.endDate);

const entriesQuery = q.Map(
  q.Paginate(
    q.Range(q.Match(q.Index("allEntries")), START_DATE, END_DATE)
  ),
  q.Lambda("entry", q.Get(q.Var("entry")))
);

const entriesResult = await client.query(entriesQuery);

console.log("ENTRIES", entriesResult);

Answer 2

You're asking your question in FQL v4, which is the prior version of Fauna's query language. For the sake of ease and clarity, I am going to first answer the question using FQL v10

In order to perform a range based query on an index, you need to define the field you want to range on as a value of the index, not a term. This following example index definition is in Fauna Schema Language (FSL).

collection Entries {
   index orderedByCreated {
     values: [ .created ]
   }
}

Note that the I have defined an index named "orderedByCreated", inside the Entries collection. The following query uses that index to perform a range based query the created field.

Entries.orderedByCreated({ 
  from: Time("2023-09-18T22:31:50.647024Z"), 
  to: Time("2023-09-18T22:32:38.070966Z") 
})

The important takeaway here is that the values you declare in an index determine how the index is sorted, and what you can range over. You can't range over term, you can only do an exact match on terms.

Here's why: An index term is an optional element of an index that used to increase the performance of an index. Fauna's query engine uses the terms you've defined in the index to search only the specific portion of the index which is needed to fulfill the query. The best performing indexes always use terms, but they are not required. More useful information is available in the indexes section of the Fauna documentation.

Here's how to solve your problem in FQL v4, but as I say, v10 is much easier to use, and I encourage you to use it over v4. Here is the index definition:

CreateIndex({
    name: "entries_by_date_only",
    source: Collection("Entries"),
    values: [{ field: ["data", "created"] }]
})

And here is the query

Paginate(
  Range(
    Match(
      Index("entriesOrderedByCreated")
    ), 
    Date("2023-08-18"), 
    Date("2023-09-18")
  )
)