CODEWITHSUNDEEP
amazon-dynamodbdynamo-localdatabasenosql
Paddy
Paddy

Reputation: 11

Whats the best way to query DynamoDB based on date range?

As part of migrating from SQL to DynamoDB I am trying to create a DynamoDB table. The UI allows users to search based on 4 attributes start date, end date, name of event and source of event. The table has 6 attributes and the above four are subset of it with other attributes being priority and location. The query as described above makes it mandatory to search based on the above four values. whats the best way to store the information in DynamoDB that will help me in querying based on start date and end date fairly easy. I thought of creating a GSI with hashkey as startdate, rangekey as end date and GSI on the rest two attributes ?

Inshort: My table in DynamoDB will have 6 attributes EventName, Location, StartDate, EndDate, Priority and source.

Query will have 4 mandatory attributes StartDate, EndDate, Source and Event Name.

Thanks for the help.

Upvotes: 0

Views: 885

Answers (1)

JoeFrizz
JoeFrizz

Reputation: 711

You can use greater than/less than comparison operators as part of your query http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/QueryAndScan.html

So you could try to build a table with schema: (EventName (hashKey), "StartDate-EndDate" (sortKey), other attributes)

In this case the sort-key is basically a combination of start and end date allowing you to use >= (on the first part) and <= (on the second part)... dynamodb uses ASCII based alphabetical ordering... so lets assume your sortKey looks like the following: "73644-75223" you could use >= "73000-" AND <= "73000-76000" to get the given event.

Additionally, you could create a GSI on your table for each of your remaining attributes that need to be read via query. You then could project data into your index that you want to fetch with the query. In contrast to LSI, queries from GSI do not fetch attributes that are not projected. Be aware of the additional costs (read/write) involved by using GSI (and LSI)... and the additional memory required by data projections...

Hope it helps.

Upvotes: 1

Related Questions