Amazon DynamoDB customers often need to work with dates and times in DynamoDB tables. Querying tables on the basis of year, month, day, or even hours and minutes for real-time workloads has become a necessity for organizations. Therefore, it’s important to be able to efficiently query date and time data inside a DynamoDB table.
DynamoDB supports multiple data types, including:
Scalar types – Number, string, binary, Boolean, and null
Document types – List and map
Set types – Sets of number, string, and binary values
DynamoDB doesn’t have a dedicated datetime data type. In this post, we demonstrate how to query date and time data through a use case of traffic cameras placed on a busy highway to record the speed of cars in order to identify vehicles that are exceeding the speed limit (speeding). You’ll walk through using datetime and timestamp models to retrieve data from tables.
Prerequisites
To complete the examples in this post, you must meet the following requirements:
An AWS account (with DynamoDBFullAccess and AWSCloud9Administrator Permissions).
An AWS Cloud9 instance.
The latest version of AWS Command Line Interface (AWS CLI).
The examples in this post use AWS Cloud9 to demonstrate working with datetime data types in DynamoDB. Optionally, you can use your local machine’s terminal.
ISO 8601
You can store date and time data as either a string or numeric data type within DynamoDB. For date and time data stored as a string, follow the ISO 8601 standard. ISO 8601 is an international standard covering the worldwide exchange and communication of date and time-related data.
ISO 8601 represents date and time by starting with the year, followed by the month, day, hour, minutes, seconds, and milliseconds.
2022-01-10 – This is a calendar date and no time, indicating January 10, 2022
2022-09-15T08 – This is a calendar date with time. The hour is separated from the date by a T. This example is interpreted as 8:00 AM on September 15, 2022.
When no time is specified, the time is presumed to be 00:00 in the local time zone. ISO 8601 supports several other date and time formats.
Working with datetime data
The test table for using datetime has the attributes ID (camera ID), DT (date and time), Location, Vehicle# (vehicle number) and VehicleSpeed. ID is the partition key and DT is the sort key, which are combined to act as a composite primary key.
ID
DT
Location
Vehicle#
VehicleSpeed
A2
2020-11T10:07:22
Street B
HJ-6789-SA
79mph
A2
2021-02-21T18:08:22
Street B
PT-9754-MA
87mph
In support of the use case, it makes sense to keep date and time as the sort key to the granular extent of seconds. For example, the camera with ID A2 can record a vehicle on July, 13, 2022 at 9:23:45 and the same camera can capture another vehicle at the same date but at 9:23:47, thereby creating two unique records.
From a business logic standpoint, this makes sense as each camera has a unique ID assigned to it, and using the camera’s metadata, you can know the camera’s location. Date and time is used to determine when a record was created. You can then query the DynamoDB table to extract information such as:
The number of vehicles recorded on or after a specific date, for example, July 12, 2022, that exceeded the speed limit.
The number of vehicles recorded speeding in a specific month.
Using date and time as the primary key isn’t efficient as it would increase the cardinality and number of partitions needed to physically store the data. Some days could have more traffic than others, thereby creating unbalanced partitions.
Let’s test the use case with a practical demonstration. To start, you’ll create a test table called date-time-test and load it with data. After creating the table and verifying that the data has been successfully loaded, you’ll run a series of queries and scans to familiarize yourself with methods for retrieving datetime data from a DynamoDB table.
Note: This walkthrough will incur a cost of less than $1.
Prepare the date-time-test table
Use the following command to create a DynamoDB table named date-time-test:
Open the AWS Management Console and navigate to DynamoDB in your AWS Region and verify the table creation.
Create a file named load.json with the following content:
Load the data to the DynamoDB table named date-time-test using the following AWS CLI command:
Use the DynamoDB console to select the date-time-test table. Under Explore table items verify that the sample data was loaded successfully.
Use queries to retrieve data from date-time test
In this section, you’ll run a series of queries to retrieve datetime data from a DynamoDB table.
Retrieve the vehicle data for camera A1 after July 12, 2022. The table sort key is DT (DateTime) represented as a string data type. You can specify a key condition expression to return only the records that were created after a specific date by adding a sort key condition.
The query returns two items: Vehicle# AB-1234-SA and FS-1234-WE with VehicleSpeed 90mph and 50mph at Street A and Street C respectively. Also note the use of –return-consumed-capacity to report the read capacity units (RCUs) consumed from the table.
Next, retrieve the vehicle details captured by camera A1 after July 13, 2022, at 6:10:24 PM.
There is only one item in the table at that date and time, and it has VehicleSpeed 68mph and Vehicle# JY-4509-EB.
The following query demonstrates what happens when there are no matching items.
The DT value is September 15, 2021, at 9:12:10 AM, which doesn’t exist in the table. The command doesn’t return any items and uses 0.5 RCUs.
Use scans to retrieve data
The query operation requires an exact partition key value. If the partition key value isn’t known or if a lookup condition is based on a non-key attribute, you need to use the scan operation. Running scan operations on tables can be expensive and inefficient, and so the key schema and indexes should be designed with access patterns in mind.
Retrieve the vehicle details for the month of July 2022, irrespective of camera ID. Run the following scan:
In the preceding scan, the between operator defined the timeframe. There are four matching records for the month of July 2022 in the table. A partition key wasn’t used because, unlike in a query, a partition key isn’t mandatory in a scan.
Retrieve all vehicles traveling faster than 70 mph during the month of July 2022.
The previous scan returns only one item: Vehicle # AB-1234-SA, which was traveling at 90 mph on July 13, 2022, at 3:07:22 PM.
To retrieve the vehicle speed for all vehicles from 6:00:00 PM to 6:59:59 PM on any day of any year, run the following scan:
The previous scan returns five items. All of them have a datetime value that contains 18. Notice the scan uses the contains operator.
Retrieve the details of vehicles traveling faster than 70 mph after July 12, 2022.
Only one vehicle with number AB-1234-SA was traveling faster than 70 mph after July 12, 2022.
Retrieve the vehicle details for the year 2022.
The scan for all vehicles in the year 2022 returns four items.
Retrieve the details of vehicles traveling faster than 70 mph for the year 2020.
The scan returns only one item: Vehicle# HJ-6789-SA, which was recorded traveling at 79 mph.
There are several comparison operators available for the string data type in DynamoDB tables, including EQ, NE, LE, LT, GE, GT, NOT_NULL, NULL, CONTAINS, NOT_CONTAINS, BEGINS_WITH, IN, and BETWEEN.
Working with timestamp data
Perhaps the most convenient way to store timestamp data is to use the number data type to store epoch time because timestamp is numeric data by characteristic. A Unix timestamp can be stored in seconds, milliseconds, microseconds, or nanoseconds, depending on the level of precision required for the use case. For this example, the timestamp is stored in seconds.
For most use cases, it makes sense to use the timestamp as a sort key instead of the partition key because there would be too many partitions with much less data if the timestamp was used as a partition key.
In a variation of the example of speeding vehicles, let’s store similar data in a new table named timestamp-test. This time, instead of storing date and time in string format, date and time is stored in number format.
ID
ts
VehicleSpeed
3
1657909482
87mph
2
1657909430
63mph
Prepare the timestamp-test table
Use the following code to create a table with camera_Id as the partition key of number data type and ts (timestamp) as the sort key, also of number data type:
Navigate to Tables in the DynamoDB console and verify that timestamp-test has been created.
To store some data in our table, create a file named load1.json with the following content:
Run the following command to load the data:
Go to the DynamoDB console and timestamp-test table. Under Explore Table Items verify that the sample data is successfully loaded.
Use queries and scans to retrieve data from timestamp-test
In this section, you’ll run a series of queries and scans to retrieve timestamp data from timestamp-test.
Query the data to find all vehicle details between timestamp 1657909412 (July 15, 2022, 6:23:32 PM GMT) and 1657909466 (July 15, 2022, 6:24:26 PM GMT) for camera 1.
The query returns two items from the table between the two timestamps for camera 1.
Run the following scan to find all vehicles travelling faster than 70mph before 1657909482 (July 15, 2022, at 6:24:42 PM) for camera 1.
This returns three items.
Retrieve the details of over-speeding vehicles after timestamp 1657909466 which translates to Friday, July 15, 2022 6:24:26 PM
This command returns only one item from the table with Vehicle Speed of 87mph which is for camera_id 3.
Cleaning up
To avoid unnecessary cost, delete the date-time-test and timestamp-test DynamoDB tables. Also delete the Cloud9 environment.
aws dynamodb delete-table –table-name date-time-test
aws dynamodb delete-table –table-name timestamp-test
Conclusion
Working with datetime and timestamp data types within a DynamoDB table is relatively straightforward. In this post, you learned how to use various comparison operators to achieve the desired results, despite not having a dedicated data type for date and time data and instead storing the data as strings represented as defined by the ISO 8601 standard and as numbers using epoch time.
To learn more, see Supported data types and naming rules in Amazon DynamoDB.
If you have any comments or questions, leave a comment in the comments section. You can find more DynamoDB posts in the AWS Database Blog.
About the Author
Puneet Sharma works with the AWS Training and Certification Team and delivers courses on technologies including big data, machine learning, and developing on AWS to customers. Having extensively interacted with customers, he focuses on delivering solutions to customers of Amazon DynamoDB, AWS Lambda, Amazon SageMaker, and more.
Read MoreAWS Database Blog
