Is it possible to generate a BETWEEN query on a DynamoDB Global Secondary Index using an exclusive endpoint?

Question

By default, the endpoints of a BETWEEN query are inclusive. I want to query for a range of datetimes using an exclusive endpoint.

I'm currently using pynamo, so my query is:

Item.my_index.query(
    hash_key=hash_key_id,
    range_key_condition=Item.create_date.between(start_datetime, end_datetime)
)

I don't want to include items that have a create_date of end_datetime. Less than ideal options, in my current order of preference, are explicitly filtering out items with create_date == end_datetime after the query, removing create_date from the index and achieving the result with a filter condition, and adjusting the end_datetime by subtracting a microsecond before sending into the query.

Is there any way to support this directly, or a suggestion on a preferred workaround?

Answer 1

My preferred method is subtracting a microsecond from the end_datetime you send to the query. This will work well if your range key is a number. If it was a string or binary it wouldn't work as well.

The next best option would be to add a FilterExpression to remove the records where create_date == end_datetime.

Removing the create_date from the index isn't ideal because then your query will fetch all the records with the same Partition/hash key which could become unnecessarily expensive as your data grows.