CODEWITHSUNDEEP
dateamazon-dynamodb
Santthosh
Santthosh

Reputation: 1093

Setting up indexes for date comparison in DynamoDB

How do I setup an index on DynamoDB table to compare dates? i.e. for example, I have a column in my table called synchronizedAt, I want my query to fetch all the rows that were never synchronized (i.e. 0) or weren’t synchronized in the past 2 weeks i.e. (new Date().getTime()) - (1000 * 60 * 60 * 24 * 7 * 4)

Upvotes: 1

Views: 584

Answers (2)

skvp
skvp

Reputation: 2000

I had similar requirement to query on the date range. In my case date range was only criteria. The issue with DynamoDB is you cannot create an Index with Just Range key. It always require Hashkey and Query on such index always expect equal to condition for Hashkey.

So I tricked the DB. I created a Key as Century and populated with century year of the date. For example 1 Jan 2019, century key value is 20. For 1 Jan 2020 century key value is also 20. Very easy to derived from any date. Then Created GSI with Hashkey as Century and RangeKey as date. While querying it is very easy to derive century from date range and Build query condition Hashkey as century and date range. Since I am dealing with data no more than 5 years, trick won't fail for next 75 years. :)

It is not so "nice to have" workaround but it work for me quite well. May be it will help someone else as well.

Upvotes: 1

matteo
matteo

Reputation: 469

It depends by the other attributes of your table.

You may use an Hash and Range Primary Key if the set of Hash values is relatively small and stable; in this case you could filter the dates by putting them in the Range, but anyway the queries will be done by specifying the Index also, and because of this it may or may not make sense to pre-query for all the Index values in order to perform a loop where ask for the interesting Range (inside of the loop for each Index value).

An alternative could be an Hash and Range GSI. In this case you might put a fixed dumb value as Index, in order to query for the range of all Items at once.

Lastly, the less efficient Scan, but with large tables it will be a problem (the larger the table the more time the Scan will take to complete).

Upvotes: 1

Related Questions