2

I'm new to Dynamo and have been trying to query the DB for a range.

enter image description here

I'm doing a query for an ISO date string in my Lambda like this:

  const start = event['queryStringParameters']['startDate'];
  const end = event['queryStringParameters']['endDate'];
  
  let params = {
    TableName: "Parcel",
    KeyConditionExpression: "#trackNo = :trackNo AND #ShipmentDate BETWEEN :start AND :end",
    ExpressionAttributeNames: {
      "#trackNo": "TrackingNumber",
      "#ShipmentDate": "ShipmentDate"
    },
    ExpressionAttributeValues: {
      ":trackNo": "TrackingNumber",
      ":start": start,
      ":end": end
    }

but I am not getting any records in the range...

I've searched this for hours... I cannot figure out how to perform this query.

Example Data:

{
  "Carrier": "FedEx",
  "ShipmentDate": "2019-09-02",
  "TrackingNumber": "TN_2019-09-02"
}

Functioning scan paradigm:

  let params = {
    TableName: "Parcel",
    FilterExpression: "#ShipmentDate BETWEEN :start AND :end",
    ExpressionAttributeNames: {
      "#ShipmentDate": "ShipmentDate"
    },
    ExpressionAttributeValues: {
      ":start": start,
      ":end": end
    }
};

  try {
    const data = await documentClient.scan(params).promise();
    responseBody = JSON.stringify(data);
    
    statusCode = 200;
  } catch (err) {
    responseBody = `Unable to get shipments: ${err}`
    statusCode = 403;
  }
Jim from Princeton
  • 721
  • 1
  • 9
  • 19
  • The query looks OK. Are you getting an error, or just an empty result set? Can you share what your data looks like? It's hard to tell what issue you're facing from the query alone. – Seth Geoghegan Sep 08 '20 at 17:06
  • 1
    It sounds like the date your start and end might be in a slightly different format. Make sure you don't have format differences between `ShipmentDate`, `:start` and `:end`. Not all ISO 8601 strings are made equal, check timezone, milliseconds, etc. What you're doing is correct in principal, I use ISO strings for dates with DDB in this manner all the time. – Richard Dunn Sep 08 '20 at 17:57
  • I’m getting an empty results set. If I use scan, it will work but that’s expensive. My ISO date is e.g. “2020-08-15”, a string. Thanks Richard Dunn and @Seth Geoghegan. I’m sure that the query paeans are correct. Frustrating. – Jim from Princeton Sep 08 '20 at 22:20
  • I agree with Richard. Perhaps you've already done this, but print out the values of `params`, `start` and `end` to compare them with what you have stored in DynamoDB. I suspect it's a timestamp format issue. – Seth Geoghegan Sep 08 '20 at 22:33
  • @SethGeoghegan I added an example element and a functioning ```scan``` approach. – Jim from Princeton Sep 09 '20 at 09:30

0 Answers0