How to query dates in DynamoDB - Between, After, and Before

In this article, we will discuss how to query dates in dynamodb.

The data type for storing dates in DynamoDB:

There are different data types(string, number, binary, boolean, list, map, set, etc..)available in DynamoDB. We can store the dates  in ISO string format so that they can be both human-readable and easy to query.

DynamoDB table creation

Before querying for dates in the DynamoDB table, let's create a simple DynamoDB and insert some records so that we can discuss different queries

We're going to create a simple DynamoDB table with userId as Partition Key and orderDate as Sort Key

Inserting records:

We can use the below code for inserting records into the DynamoDB table

const region = "us-east-1";
const TABLE_NAME = "ddb-dates";
const ddbClient = new DynamoDBClient({ region });

export interface IOrderItem {
  userId: string;
  orderDate: string;
}

export async function putItem(item: IOrderItem) {
  const params: PutItemCommandInput = {
    TableName: TABLE_NAME,
    Item: marshall(item),
  };
  const command = new PutItemCommand(params);
  await ddbClient.send(command);
}

async function insertData() {
  const orderItems: IOrderItem[] = [
    {
      userId: "user1",
      orderDate: new Date(2021, 1, 1).toISOString(),
    },
    {
      userId: "user1",
      orderDate: new Date(2021, 1, 2).toISOString(),
    },
    {
      userId: "user1",
      orderDate: new Date(2021, 1, 3).toISOString(),
    },
    {
      userId: "user1",
      orderDate: new Date(2021, 1, 4).toISOString(),
    },
    {
      userId: "user1",
      orderDate: new Date(2021, 1, 5).toISOString(),
    },
  ];

  for (const item of orderItems) {
    await putItem(item);
  }
}

Please note that we're inserting the date in ISO string format. Once you have inserted the data, you can verify the same in the AWS console

Note: I'm located in Australia, so the ISO format may seem to have a time difference between the date that I inserted the record in dynamodb.

Querying records before a specific date:

Our sort key is orderDate and you can use < symbol to query records before a particular date.

Below is the code example

export async function queryBeforeOrderDate(userId: string, orderDate: string) {
  const params: QueryCommandInput = {
    TableName: TABLE_NAME,
    KeyConditionExpression: "userId = :userId AND orderDate < :orderDate",
    ExpressionAttributeValues: marshall({
      ":userId": userId,
      ":orderDate": orderDate,
    }),
  };
  const command = new QueryCommand(params);
  const data = await ddbClient.send(command);
  return data.Items?.map((item) => unmarshall(item));
}

Below is the result of the above code

[
  { orderDate: '2021-01-31T13:00:00.000Z', userId: 'user1' },
  { orderDate: '2021-02-01T13:00:00.000Z', userId: 'user1' }
]

Querying records after a specific date:

Our sort key is orderDate and you can use > symbol to query records before a particular date.

Below is the code example

export async function queryAfterOrderDate(userId: string, orderDate: string) {
  const params: QueryCommandInput = {
    TableName: TABLE_NAME,
    KeyConditionExpression: "userId = :userId AND orderDate > :orderDate",
    ExpressionAttributeValues: marshall({
      ":userId": userId,
      ":orderDate": orderDate,
    }),
  };
  const command = new QueryCommand(params);
  const data = await ddbClient.send(command);
  return data.Items?.map((item) => unmarshall(item));
}

Below is the result of the above code

[
  { orderDate: '2021-02-02T13:00:00.000Z', userId: 'user1' },
  { orderDate: '2021-02-03T13:00:00.000Z', userId: 'user1' },
  { orderDate: '2021-02-04T13:00:00.000Z', userId: 'user1' }
]

Querying records between a start date and an end date:

Our sort key is orderDate and you can use BETWEEN operator to query records between different dates

Below is the code example

export async function queryBetweenOrderDates(
  userId: string,
  startDate: string,
  endDate: string
) {
  const params: QueryCommandInput = {
    TableName: TABLE_NAME,
    KeyConditionExpression:
      "userId = :userId AND orderDate BETWEEN :startDate AND :endDate",
    ExpressionAttributeValues: marshall({
      ":userId": userId,
      ":startDate": startDate,
      ":endDate": endDate,
    }),
  };
  const command = new QueryCommand(params);
  const data = await ddbClient.send(command);
  return data.Items?.map((item) => unmarshall(item));
}

Below is the result of the above code

[
  { orderDate: '2021-01-31T13:00:00.000Z', userId: 'user1' },
  { orderDate: '2021-02-01T13:00:00.000Z', userId: 'user1' },
  { orderDate: '2021-02-02T13:00:00.000Z', userId: 'user1' }
]