Range query on cloudsearch date

Question

I have a dynamodb table which stores creation_date epoch in string format. This date is neither hash key nor sort key. Ultimate goal is querying the creation_date for a range i.e. I need all the ids in the give time range.

The table schema is: id, version, creation_date, info.

id is hash key and version is sort key.

I was thinking of creating a cloudsearch domain and link that to dynamodb table. Is it possible to use a range query in cloudsearch using java if the date is in string format? If yes how?

Having used DynamoDB and CloudSearch in production, I’d recommend sticking with just DynamoDB if all you need is that one query. You can accomplish it with a single GSI, which will be much simpler and probably a lot more cost effective than using CloudSearch. If you want, you could provide some more information about your table, and I could give a more detailed explanation. — Matthew Pope
Updated the question with the schema. I cannot create a gsi with creation_date as hash key because it will not allow query by range. While querying I do not have id. That is why I cannot create a gsi with id as hash key and creation_date as sort key. — user2803056
It’s still possible. What’s the typical size of time range you will be querying? What is your rate of items being created? — Matthew Pope
time range is around 2 weeks. Usually around 300 entries are created in an hour. But that may vary. — user2803056

Matthew Pope Matthew Pope · Accepted Answer · 2019-02-28T05:30:09

Here’s how you can accomplish this in DynamoDB using a GSI with a hash key of creation_y_m and a GSI range key of creation_date.

When you’re querying for a range of creation dates, you need to do a bit of date manipulation to find out all of the months in between your two dates, but then you can query your GSI with a key condition expression like this one.

creation_y_m = 2019-02 AND creation_date BETWEEN 2019-02-05T12:00.00Z AND 2019-02-18T06:00:00Z

Given that most of your queries are a two week range, you will usually only have to make only one or two queries to get all of the items.

You may need to backfill the creation_y_m field, but it’s fairly straightforward to do that by scanning your table and updating each item to have the new attribute.

There are, of course, many variations on this. You could tweak how granular your hash key is (maybe you want just year, maybe you want year-month-day). You could use epoch time instead of ISO 8601 strings.

Range query on cloudsearch date

1 Answers