Beginner question: creating an index for timestamp

Help
#1

Very basic question but after playing with the docs and the shell I still can’t figure this out.

I have a collection , each document has a start_date field (I receive this from an API) which is just an ISO timestamp like "2021-02-27T17:50:00Z"

I tried creating an index for this like so:

CreateIndex({name:'activities_by_time',source:Collection('activities'),values:[{field:['data', 'start_date']}]})

I try to retrieve the most recent document using

Paginate(Match(Index("activities_by_time")),{"size":1})

which returns:

{
  after: [
    "2021-02-27T18:50:00Z",
    Ref(Collection("activities"), "291707100238709251")
  ],
  data: ["2021-02-27T17:50:00Z"]
}

and if I increase the size to 2, I get:

{ data: ["2021-02-27T17:50:00Z", "2021-02-27T18:50:00Z"]}

So I’m getting back the field that the index is on, not the ref. So obviously if I add a Map around this Paginate I get an error.

Side note, but I’m just dumping these json objects into faunadb as I receive them via a webhook - will fauna just figure out that these are dates?

#2

You can include more than one value in your index, so you could change it to look like

CreateIndex({
name:'activities_by_time',
source:Collection('activities'),
values:[
{field:['data', 'start_date']},
{field:['ref']},
]
})

And that would include the ref in the data you get back.

And a tip, you can’t update the structure of an index, so you will either have to delete the one you already made and then recreate it after a minute or so, or create an index with a new name.

#3

thank you! that’s very helpful. I did try that, but what’s the syntax for writing a Map when each item in the array is an object (both the date and the ref)? Is it like JS syntax?

#4

Hello !
The result you would get is an array of array with activities_by_time like :

[
  ["2021-02-27T18:50:00Z", Ref(Collection("activities"), "291707100238709252")],
  ["2021-02-27T17:50:00Z", Ref(Collection("activities"), "291707100238709251")]
]

So you would do :

Map(
  Paginate(Match(Index("activities_by_time"))),
  Lambda('activity', Get(Select(1, Var('activity'))
)
#5

thanks! So I hacked together a working query with a date filter, but I’m sure this can’t be the idiomatic way:

      q.Map(
        q.Filter(
          q.Paginate(q.Match(q.Index("all_activities_by_time"))),
          q.Lambda(
            ["date", "ref"],
            q.GTE(
              q.ToTime( q.Select(["data", "start_date"], q.Get(q.Var("ref")))),
              q.Time(startTime)
            )
          )
        ),
        q.Lambda(["date", "ref"], q.Get(q.Var("ref")))
      )
#6

You could use the Range function ! More details in the documentation.

q.Paginate(
    q.Range(
        q.Match(q.Index('all_activities_by_time')),
        startTime,
        []
    ),
),

Of course startTime should be the same “type” as the stored value.
So use the format "2021-02-27T18:50:00Z" etc…
I believe this will help you :slight_smile:

// Edit: I hope the date value returned in your Index is the startTime, else you will need to remake an Index I guess for easier filter & range functions.

#7

ok that looks much cleaner! will try that.

The stored value is a timestamp, stored as a string in ISO format (see first post in thread) and yes that’s what the index is on.

But the stored value is just a string, does the Range query implicitly cast it as a timestamp to do the comparison? Or are we just getting away with comparing strings because that’s equivalent with ISO format?

#8

I believe this is comparison string, just like in the example

q.Paginate(
   q.Range(q.Match(q.Index('letters')), 'F', [])
)

I am myself using Range over JS timestamps like Date.now() and it’s working fine !