How to get latest documents from FaunaDB, based on timestamp?

Question

Currently I store some data in FaunaDB every week. This is done using a cronjob. In my code I'm trying to fetch the documents from only the last two weeks. I'd like to use the timestamp to do so.

One of the documents to fetch:

{
  "ref": Ref(Collection("weeklyContributors"), "350395411XXXXXXXX"),
  "ts": 1670421954340000,
  "data": {
     ...allMyDataFields
  }
}

My code

const now = Date.now() * 1000;
const twoWeeksAgo = (Date.now() - 12096e5) * 1000;

console.log(now); //returns 1670493608804000
console.log(twoWeeksAgo); // returns 1669284008804000

// the stored document has a timestamp of 1670421954340000, so this should be in between [now] and [twoWeeksAgo]

await client.query(
      q.Paginate(
        q.Range(
          q.Match(q.Index("get_weekly_list_by_ts")),
          twoWeeksAgo,
          now
        )
      )
    );

This is a screenshot of the index I created in Fauna

Above code should fetch all documents where the timestamp's between now and twoWeeksAgo but it returns an empty array (so no documents match the query). Above code doesn't generate any errors, it does return a statuscode 200, so syntax should be fine. Why can't I fetch the document I gave in this example?

UPDATE

Found the solution for the index. The index should filter on Values, not Terms. Enter TS and Ref returns the document. BUt now I don't know how to get the corresponding document.

This returns an error

await client.query(
      q.Map(
        q.Paginate(
          q.Range(
            q.Match(q.Index("get_weekly_list_by_ts")),
            twoWeeksAgo,
            now
          )
        ),

        q.Lambda((x) => q.Get(x))
      )
    );

Changed index screenshot here

Answer 1

Congratulations on figuring out most of the answer for yourself!

As you deduced, the terms definition in an index specifies the fields to search for, and the values definition specifies the field values to return for matching entries.

Since you added the document reference to the values definition, all that you need now is to fetch that document. To do that, you need to Map over the results.

The following example uses Shell syntax, and involves sample documents that I created with a createdAt field recording the creation timestamp (since ts is the last-modified timestamp):

> Map(
  Paginate(
    Range(
      Match(Index("get_weekly_list_by_ts")),
      TimeSubtract(Now(), 14, "days"),
      Now()
    )
  ),
  Lambda(
    ["ts", "ref"],
    Get(Var("ref"))
  )
)
{
  data: [
    {
      ref: Ref(Collection("weeklyContributors"), "350498857823502848"),
      ts: 1670520608640000,
      data: { createdAt: Time("2022-12-01T17:30:08.633Z"), name: 'Fourth' }
    },
    {
      ref: Ref(Collection("weeklyContributors"), "350498864657072640"),
      ts: 1670520615160000,
      data: { createdAt: Time("2022-12-07T17:30:15.152Z"), name: 'Fifth' }
    }
  ]
}

Since your index returns ts and ref, notice that the Lambda function accepts both parameters in an array. The Lambda parameters have to match the number returned by the index. Then the Lambda calls Get to fetch the document.

In case you're wondering, here's the index definition that I used for my example:

> Get(Index("get_weekly_list_by_ts"))
{
  ref: Index("get_weekly_list_by_ts"),
  ts: 1670520331720000,
  active: true,
  serialized: true,
  name: 'get_weekly_list_by_ts',
  source: Collection("weeklyContributors"),
  values: [ { field: [ 'data', 'createdAt' ] }, { field: [ 'ref' ] } ],
  partitions: 8
}

My index is misnamed: I used the same name from your original query to help you correlate what is being used.

Note: there is no need to mask the document ID in a document that you share. It is only valid for the database containing the document.