Date Query in dynamoDB using R

Question

I want to query dates before, after and between specified dates. My table has as partition key "UUID" and sort key "ProductID" (both strings), and added a GSI (named "Date-ProductID-index") that uses as PK "Date" and SK "ProductID"

So far, I've been able to query the rows with a specific date using:

svb <- paws::dynamodb()

expression_attribute_names <- list(
  "#DateAttr" = "Date" 
)

table_name <- "sales-template"
start_date <- "2023-08-02T12:00:15.075783"
end_date <- "2023-08-02T16:19:15.075783"

result <- svb$query(
  
  TableName = table_name,
  IndexName = "Date-ProductID-index",
  KeyConditionExpression = " #DateAttr = :dateValueA ",
  ExpressionAttributeNames = expression_attribute_names,
  ExpressionAttributeValues = list(":dateValueA" = list(S = start_date))
  
)

However, as soon as I change the "=" to ">" to get the entries with dates after the specified date, I get the error: "Error: com.amazon.coral.validate (HTTP 400). Query key condition not supported":

svb <- paws::dynamodb()

expression_attribute_names <- list(
  "#DateAttr" = "Date" 
)

table_name <- "sales-template"
start_date <- "2023-08-02T12:00:15.075783"
end_date <- "2023-08-02T16:19:15.075783"

result <- svb$query(
  
  TableName = table_name,
  IndexName = "Date-ProductID-index",
  KeyConditionExpression = " #DateAttr > :dateValueA ",
  ExpressionAttributeNames = expression_attribute_names,
  ExpressionAttributeValues = list(":dateValueA" = list(S = start_date))
  
)

"Date" is in ISO 8601 format, and according to this tutorial, the query should work

Answer 1

You cannot do conditions on a Partition key, not without scanning the entire table.

What you need to do is create an index with a static value as partition key, let's say GSI1PK=1 for all items. Then use date as sort key.

Now you can efficiently Query where GSI1PK=1 AND date >< or between.

Be warned that this approach will cap your wrote throughput to 1000WCU.