CODEWITHSUNDEEP
amazon-web-servicesamazon-dynamodb
Gustav Vingtoft
Gustav Vingtoft

Reputation: 409

AWS DynamoDB | How to query timestamps between two dates?

I have a tabel with users, where I wish to query all users created in f.x April.

When the user is being created, a timestamp is automatically created for that user.

I made an index in my table, with timestamp as partition key and id as sort key.

The timestamp is in unix miliseconds.

This is my code for this query:

GetUsersOnTimestamp(): Promise<any> {
    return new Promise( (resolve, reject) => {

        const _dynamoDB = new AWS.DynamoDB.DocumentClient();
        const startDate = 1554069600000;
        const endDate = 1556661600000;

        const params = {
            TableName: 'user-table',
            IndexName: 'timestamp-id-index',
            KeyConditionExpression: '#timestamp = :hkey BETWEEN :sdate AND :edate',
            ExpressionAttributeNames: {
                '#timestamp': 'timestamp'
            },
            ExpressionAttributeValues: {
                ':hkey': 'timestamp',
                ':sdate': startDate,
                ':edate': endDate,
            }
        };

I get the following error:

ExpressionAttributeValues contains invalid key: Syntax error; key: "hkey"

Upvotes: 2

Views: 4588

Answers (1)

Dunedan
Dunedan

Reputation: 8435

You can't conditionally query for your partition key. You have to specify a full partition key value without any condition. The BETWEEN comparison operator is only available for querying the sort key conditionally.

From the DynamoDB documentation:

You must specify the partition key name and value as an equality condition.

You can optionally provide a second condition for the sort key (if present). The sort key condition must use one of the following comparison operators:

  • a = b — true if the attribute a is equal to the value b
  • a < b — true if a is less than b
  • a <= b — true if a is less than or equal to b
  • a > b — true if a is greater than b
  • a >= b — true if a is greater than or equal to b
  • a BETWEEN b AND c — true if a is greater than or equal to b, and less than or equal to c.

The following function is also supported:

  • begins_with (a, substr) — true if the value of attribute a begins with a particular substring.

To get the ability to query for a range of timestamps is not straight forward to achieve with DynamoDB. One solution would be to add an additional field to your items which contains just year and month of your timestamp. You could then create a global secondary index (GSI) with the year-month-field as primary key and the full timestamp as sort key. With this approach you could query all users created in a given month.

Upvotes: 4

Related Questions