DynamoDb query on stored date

Question

I have resolving template that will get start date as date from appsync query. But my date saved in Dynamo is having datetime stamp. How we can compare these two dates without time. Below is my resolver template query:

 "version": "2018-05-29",
 "operation": "Query", 
 "query": {
  "expression": "Id = :Id and startDate =:date",
  "expressionValues": {
    #set( $Id = 
      $util.dynamodb.toDynamoDBJson("fixture#${ctx.args.input.Id}") )
    ":Id": $Id,
    #set( $date = $util.dynamodb.toDynamoDBJson($ctx.args.input.date)
    ":date": $date
  }
}

score 2 · Answer 1 · answered Jun 03 '20 at 06:56

DynamoDB does not understand dates. Based on your requirements, there are two ways you can achieve this depending upon the format of the timestamp in your DynamoDB.

1 - If you store timestamp as "milliseconds/seconds since epoch", you can convert the AWSDate (which is ISO8601) to Epoch milliseconds/seconds timestamp using

$util.time.parseISO8601ToEpochMilliSeconds("2018-02-01T17:21:05.180+08:00")

and then use filter with BETWEEN :startDate AND :endDate to query your table based on the input date. Please note that

endDate(seconds) = startDate + 86400

or

endDate(milliseconds) = startDate + 86400000

2 - If you use String representation of timestamp, then you might need to add Global Secondary Index (GSI) with a date field as attribute of keytype "range". There are some very good explanation and suggested solution on this link, this link and this link.

I hope this helps you.

DynamoDb query on stored date

1 Answers1