DynamoDB query/sort based on timestamp

Question

In DynamoDB, I have a table where each record has two date attributes, create_date and last_modified_date. These dates are in ISO-8601 format e.g. 2016-01-22T16:19:52.464Z.

I need to have a way of querying them based on the create_date and last_modified_date e.g.

• get all records where create_date > [some_date]
• get all records where last_modified_date < [some_date]

In general, I need to get all records where [date_attr] [comparison_op] [some_date].

One way of doing it is to insert a dummy fixed attribute with each record and create an index with the dummy attribute as the partition key and the create_date as the sort key (likewise for last_modified_date.)

Then I'll be able to query it as such by providing the fixed dummy attribute as partition key, the date attributes as the sort key and use any comparison operators <, >, <=, >=, and so on.

But this doesn't seem good and looks like a hack instead of a proper solution/design. Are there any better solutions?

Answer 1

There are some things that NoSQL DBs are not good at, but you can solve this with the following solutions:

1. Move this table data to SQL database for searching purpose: This can be effective because you will be able to query as per your requirement, this might be tedious sometimes because you need to synchronize the data between two different DBs

2. Integrate with Amazon CloudSearch: You can integrate this table with CloudSearch and then rather than querying your DynamoDB table you can query Cloudsearch

3. Integrate with Elasticsearch: Elasticsearch is similar to CloudSearch although each has pros and cons, the end result would be same - rather than querying DynamoDB, instead query Elasticsearch

4. As you have mentioned in your question, add GSI indexes