How to query: filtering on multiple primary partition keys and range on primary sort key

Question

Background I have a dynamoDB table consisting of a Primary Partition Key clientID ("N") and a Primary Sort Key createdAt("S") (as UTC ISO 8601 date) + some additional attributes.

Problem I Want to query back, all items where createdAt BETWEEN two dates and only for around 100 specific clientIDs at a time.

• Dead end 1: First I thought about using BatchGetItem, however though I know what clientIDs I want to query, I cannot specify the sort keys, since I'm looking for anything, that happen within a timeframe.
• Dead end 2: Secondly I though about using Query, however that one does not allow me to look for multiple clientIDs at the same time (in looked at the ´IN´ operator, but that one does not do what I was hoping for). So in other words, how can I achieve this, without having to do a Query for every single ClientID in the timeframe?

Answer 1

I ended up doing something else:

I added a new attribute on called index_date which only holds the date part of created_at (not time). Then I added a new secondary global index on this attribute, with created_at as sort key. In this way, I can efficiently query back all items for one day (in a specific time frame if I want) at a time. Then process them afterwards.