Question 422131 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – How to filter by date range in dynamodb?

I have following data in dynamo , all string fields.

id |   data    | my_date 

1  | some text | 2024-03-21
2. | more text | 2024-09-30
....

i wrote a query as such, to scan items and then filter by date range. I tried this and it seems it works. is there any other way to add a filter like if there I can do BETWEEN start AND end dates?

also, when i run this query, i suppose it scans all the items in my table and then filter based on my date range. is there a different(possibly better ) way to filter based on date range?

{
   TableName: mytable, 
   FilterExpression:'my_date > start AND my_date < end',
   ExpressionAttributeValues: {
        ':start': {'S': '2024-06-30'},
        ':end'  : {'S': '2024-01-01'}
   }
}
Tags:

2

Answers


  1. 0

    is there any other way to add a filter like if there I can do BETWEEN start AND end dates?

    DynamoDB supports BETWEEN keyword as mentioned here

    also, when i run this query, i suppose it scans all the items in my table and then filter based on my date range. is there a different(possibly better ) way to filter based on date range?

    IMO if this is not a frequent query pattern it might be a reasonable choice.

    Otherwise:

    • Whether you can avoid scan operations depends on how primary key is designed inside the table.

    • I would have used a composite primary key (partition key + sort key)

      Partition Key groups items you want to query together logically.
      Sort Key stores the my_date value, allowing range queries.

    Then, you can use the Query operation with a KeyConditionExpression for efficient date range filtering.

    I would recommend to read aws blog for working with timestamp in order to optimize your filtering with date range: https://aws.amazon.com/blogs/database/working-with-date-and-timestamp-data-types-in-amazon-dynamodb/

    Login or Signup to reply.
  2. 0

    My guess here is that you want to get all items between a date range. This is known as global sorting which you are unable to do in your current schema, at least you can’t do it efficiently without the need to scan.

    You can create a global secondary index where all your items share a partition key and timestamp as the sort key, this gives you all items grouped by a partition key and you can do an efficient Query to get all items between two dates.

    Have a read of this blog that goes into more detail:

    https://aws.amazon.com/blogs/database/effective-data-sorting-with-amazon-dynamodb/

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search