AppSync - query for all items created within a date range?

Question

I'm trying to query my items (which have fields of AWS DateTime of CreatedAt and UpdatedAt) for all within a certain date range. For example, the past 48 hours.

For example, using this schema:

type Note @model @searchable @auth(rules: [{ allow: owner }]) {
  id: ID!
  note: String
  createdAt: AWSDateTime

I'm able to search for dates using, for example:

query {
  searchNotes(filter:{createdAt: { matchPhrasePrefix: "2018-12-27"}}) {
    items{
      id
        title
      createdAt
    }
  }
}

Which returns all notes that match the UTC time with that string prefix.

From which, I have to sort myself using moment.diff(), or some other method.

I'm not sure there is a better/more efficient way of doing searching/filtering by dates and time using AppSync and GraphQl?

Thank you.

Cloutman

Answer 1

As of writing (Jan 3, 2019), the easiest way to do this would be to store the date as an integer (e.g. seconds since epoch) which would open up the gt, lt, gte, ... filter fields on the auto-generated search resolvers filter input.

Another solution is to write your own resolver using the AWS AppSync console or your own CloudFormation stack. When writing your own resolver you can leverage the entire Elasticsearch DSL to implement all kinds of queries (see https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html). To go down this route, you can add your own search field to the Query type in your schema and write a custom resolver.

type Query {
  searchNotesByCreatedAt(start: String!, end: String!): NotesConnection
}

Then via the console or via your own CloudFormation stack you can write a resolver like this:

{
  "version": "2017-02-28",
  "operation": "GET",
  "path": "/note-<your-api-id>/doc/_search", // created by amplify
  "params": {
    "body": {
      "sort": [{ "createdAt" : {"order" : "desc"}}],
      "query": {
        "range" : {
            "createdAt" : {
                "gte": $ctx.args.start, 
                "lte": $ctx.args.end
            }
        }
      }
    }
  }
}

You will only need to deploy this resolver using the console or your own CF stack temporarily. Work is being done to allow you to write your own resolvers from within the amplify CLI. For more information on how this will work see https://github.com/aws-amplify/amplify-cli/issues/574.

Let me know if this works for you.

Lifetime

This is a thorough, and very beneficial answer. Thank you! I actually did end up doing the first method you recommended, by storing the date in Unix Epoch time, which is an integer and sorting that way. – Cloutman

Custom resolvers are now supported, here's how: aws-amplify.github.io/docs/cli/… – Hanaper

Answer 2

You can use this query to filter 2 AWSDateTime:

query {
  searchNotes(filter:{createdAt: { between: ["2018-12-27T00:00:00", "2019-01-27T00:00:00"]}}) {
    items{
      id
        title
      createdAt
    }
  }
}

Plain

Thanks. Is this a new feature/ability? – Cloutman

@stephenlizcano I don't think so, AWSDateTime are serialized in sort-able string so you can use same operations than strings operations – Plain

This is the correct way and should be the top answer. Can also be used on AWSDate fields. – Greenstone

This is no longer possible using ElasticSearch, as the between predicate has been removed. However, it can be done using a list query. – Blanka

to access createdAt from graphQL you have to add it to your model schema. Keep in mind, this might allow these sections to be updated where they were originally protected. – Benzaldehyde

Hi @aguafrommars i know this answer is last year, but i have 1 question which related with your answer. Can we just filter "date" only without seconds at the back? I tried between: ["2018-12-27", "2019-01-27"] but it doesn't work. Thanks – Macnamara

@FaiZalDong actually I don't know. Did you try to use an AWSDate instead of AWSDateTime ? – Plain