Working with date and timestamp data types in Amazon DynamoDB

July 2024: This post was reviewed and updated for accuracy.

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 query date and time data inside a DynamoDB table.

DynamoDB supports multiple data types, including the following:

• Scalar types – Number, string, binary, Boolean, and null
• Document types – List and map
• Set types – Sets of number, string, and binary values

However, 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. We walk you through using datetime and timestamp models to retrieve data from DynamoDB tables.

Prerequisites

To complete the examples in this post, you must have the following:

• An AWS account with DynamoDBFullAccess and AWSCloud9Administrator permissions (it’s also recommended to have AdministratorAccess).
• An AWS Cloud9
• The latest version of the 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. The following are some examples:

• 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 This example is interpreted as 8:00 AM on September 15, 2022.
• 2022-09-15T08:30:25 – This is an example of a calendar date along with- hours, minutes, and seconds.

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. The following table shows example data.

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 (depicted in the preceding table).

From a business logic standpoint, this makes sense because 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 following:

• 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.

For our use case, the access pattern is based on the ID of the camera, so we’re keeping CameraID as the partition key.

Let’s test the use case with a practical demonstration. To start, create a test table called date-time-test and load it with data. After you create the table and verify that the data has been successfully loaded, you can run a series of queries and scans to familiarize yourself with methods for retrieving datetime data from a DynamoDB table.

Note that this walkthrough may incur costs: refer to Amazon DynamoDB pricing for more information.

Prepare the date-time-test table

Complete the following steps to set up your table:

1. Use the following command to create a DynamoDB table named date-time-test:

   aws dynamodb create-table --table-name date-time-test \
   --attribute-definitions AttributeName=Id,AttributeType=S AttributeName=DT,AttributeType=S \
   --key-schema AttributeName=Id,KeyType=HASH AttributeName=DT,KeyType=RANGE \
   --provisioned-throughput ReadCapacityUnits=10,WriteCapacityUnits=10

2. On the DynamoDB console, navigate to the Tables page in your AWS Region and verify the table creation.

3. Create a file named load.json with the following content:

   {
       "date-time-test": [
           {
               "PutRequest": {
                   "Item": {
                       "Id": {
                           "S": "A1"
                       },
                       "DT": {
                           "S": "2022-07-13T15:07:22"
                       },
                       "VehicleSpeed": {
                           "N": "90"
                       },
                       "Location": {
                           "S": "Street A"
                       },
                       "Vehicle#": {
                           "S": "AB-1234-SA"
                       }
                   }
               }
           },
           {
               "PutRequest": {
                   "Item": {
                       "Id": {
                           "S": "A1"
                       },
                       "DT": {
                           "S": "2021-06-11T18:08:22"
                       },
                       "VehicleSpeed": {
                           "N": "40"
                       },
                       "Location": {
                           "S": "Street B"
                       },
                       "Vehicle#": {
                           "S": "BC-2345-QW"
                       }
                   }
               }
           },
           {
               "PutRequest": {
                   "Item": {
                       "Id": {
                           "S": "A1"
                       },
                       "DT": {
                           "S": "2022-07-13T18:10:22"
                       },
                       "VehicleSpeed": {
                           "N": "50"
                       },
                       "Location": {
                           "S": "Street C"
                       },
                       "Vehicle#": {
                           "S": "FS-1234-WE"
                       }
                   }
               }
           },
           {
               "PutRequest": {
                   "Item": {
                       "Id": {
                           "S": "A1"
                       },
                       "DT": {
                           "S": "2019-01-23T04:23:24"
                       },
                       "VehicleSpeed": {
                           "N": "85"
                       },
                       "Location": {
                           "S": "Street A"
                       },
                       "Vehicle#": {
                           "S": "LP-7897-NU"
                       }
                   }
               }
           },
           {
               "PutRequest": {
                   "Item": {
                       "Id": {
                           "S": "A2"
                       },
                       "DT": {
                           "S": "2020-11-10T10:07:22"
                       },
                       "VehicleSpeed": {
                           "N": "79"
                       },
                       "Location": {
                           "S": "Street B"
                       },
                       "Vehicle#": {
                           "S": "HJ-6789-SA"
                       }
                   }
               }
           },
           {
               "PutRequest": {
                   "Item": {
                       "Id": {
                           "S": "A2"
                       },
                       "DT": {
                           "S": "2021-02-21T18:08:22"
                       },
                       "VehicleSpeed": {
                           "N": "87"
                       },
                       "Location": {
                           "S": "Street B"
                       },
                       "Vehicle#": {
                           "S": "PT-9754-MA"
                       }
                   }
               }
           },
           {
               "PutRequest": {
                   "Item": {
                       "Id": {
                           "S": "A2"
                       },
                       "DT": {
                           "S": "2022-07-13T17:10:22"
                       },
                       "VehicleSpeed": {
                           "N": "62"
                       },
                       "Location": {
                           "S": "Street B"
                       },
                       "Vehicle#": {
                           "S": "JN-8967-IO"
                       }
                   }
               }
           },
           {
               "PutRequest": {
                   "Item": {
                       "Id": {
                           "S": "A3"
                       },
                       "DT": {
                           "S": "2021-02-21T18:08:22"
                       },
                       "VehicleSpeed": {
                           "N": "72"
                       },
                       "Location": {
                           "S": "Street A"
                       },
                       "Vehicle#": {
                           "S": "RB-3468-GK"
                       }
                   }
               }
           },
                   {
               "PutRequest": {
                   "Item": {
                       "Id": {
                           "S": "A3"
                       },
                       "DT": {
                           "S": "2020-07-13T09:10:22"
                       },
                       "VehicleSpeed": {
                           "N": "45"
                       },
                       "Location": {
                           "S": "Street A"
                       },
                       "Vehicle#": {
                           "S": "HI-5634-ER"
                       }
                   }
               }
           },
           {
               "PutRequest": {
                   "Item": {
                       "Id": {
                           "S": "A4"
                       },
                       "DT": {
                           "S": "2022-07-13T18:10:24"
                       },
                       "VehicleSpeed": {
                           "N": "68"
                       },
                       "Location": {
                           "S": "Street A"
                       },
                       "Vehicle#": {
                           "S": "JY-4509-EB"
                       }
                   }
               }
           }
       ]
   }

4. Load the data to the DynamoDB table named date-time-test using the following AWS CLI command:

aws dynamodb batch-write-item --request-items file://load.json

5. On the DynamoDB console, navigate to the date-time-test.
6. Choose Explore table items and verify that the sample data was loaded successfully.

Use queries to retrieve data from date-time test

Complete the following steps to run a series of queries to retrieve datetime data from a DynamoDB table:

1. 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.

   aws dynamodb query --table-name date-time-test \
   --key-condition-expression '#Id = :Id and #DT > :ts' \
   --expression-attribute-values '{":Id" : {"S": "A1"}, ":ts" : {"S": "2022-07-12"}}' \
   --expression-attribute-names '{"#Id" : "Id", "#DT": "DT"}' \
   --return-consumed-capacity TOTAL

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.

2. Next, retrieve the vehicle details captured by camera A4 on or after July 13, 2022, at 6:10:24 PM:

   aws dynamodb query --table-name date-time-test \
   --key-condition-expression '#Id = :Id AND #DT >= :dt' \
   --expression-attribute-values \
   '{":Id" : {"S": "A4"}, ":dt" : {"S": "2022-07-13T18:10:24"}}' \
   --expression-attribute-names '{"#Id" :"Id", "#DT": "DT"}' \
   --return-consumed-capacity TOTAL

There is only one item in the table at that date and time, and it has VehicleSpeed 68mph and Vehicle# JY-4509-EB.

3. The following query demonstrates what happens when there are no matching items.

   aws dynamodb query --table-name date-time-test \
   --key-condition-expression '#Id = :Id AND #DT = :dt' \
   --expression-attribute-values \
   '{":Id" : {"S": "A1"}, ":dt" : {"S": "2021-09-15T09:12:10"}}' \
   --expression-attribute-names '{"#Id" :"Id", "#DT" : "DT"}' \
   --return-consumed-capacity TOTAL

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 GSIs to retrieve data

Global secondary indexes (GSIs) serve as a great feature within DynamoDB to retrieve data with a variety of different attributes. You can create one or more GSIs and issue query requests against these indexes in DynamoDB.

To do so, complete the following steps:

1. Create a GSI with Location as the hash key and DateTime as the sort key:

   aws dynamodb update-table \
       --table-name date-time-test \
       --attribute-definitions AttributeName=Location,AttributeType=S AttributeName=DT,AttributeType=S \
       --global-secondary-index-updates '[{
           "Create":{
               "IndexName": "loc-dt-gsi",
               "KeySchema": [
                   {
                       "AttributeName" : "Location",
                       "KeyType": "HASH"
                   },
                   {
                       "AttributeName" : "DT",
                       "KeyType" : "RANGE"
                   }
               ],
               "ProvisionedThroughput": {
                   "ReadCapacityUnits": 5, "WriteCapacityUnits": 5
               },
               "Projection": {
                   "ProjectionType": "ALL"
               }
           }
       }
   ]'

2. Use the following command to check the status of the GSI (it should change to Active):

   aws dynamodb describe-table --table-name date-time-test --query "Table.GlobalSecondaryIndexes[0].IndexStatus"

We created a GSI with Location as the partition key and DT as the sort key, allowing us to retrieve the vehicle details on the basis of Location.

3. Next, retrieve the vehicle details on Street B after July 31, 2022, from the GSI:

   aws dynamodb query \
       --table-name date-time-test \
       --key-condition-expression '#loc = :loc AND #DT < :ed' \
       --expression-attribute-values '{ ":loc" : {"S": "Street B"},
           ":ed" : {"S": "2022-07-31"} }' \
       --expression-attribute-names '{"#loc" : "Location", "#DT":"DT"}' \
       --index-name loc-dt-gsi \
       --return-consumed-capacity TOTAL

   

4. Retrieve the vehicle details between July 1, 2022, and July 31, 2022, on Street A from the GSI:

   aws dynamodb query \
       --table-name date-time-test \
       --key-condition-expression '#loc = :loc AND #DT BETWEEN :sd AND :ed' \
       --expression-attribute-values '{ ":loc" : {"S": "Street A"},
       ":sd" :{"S": "2022-07-01"}, ":ed" : {"S": "2022-07-31"} }' \
       --expression-attribute-names '{"#loc" : "Location", "#DT" :"DT"}' \
       --index-name loc-dt-gsi \
       --return-consumed-capacity TOTAL

   

The query returns two items with Vehicle# AB-1234-SA and JY-4509-EB at Street A with speeds of 90 mph and 68 mph, respectively.

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.

1. Retrieve the vehicle details for the month of July 2022, irrespective of camera ID. Run the following scan:

   aws dynamodb scan --table-name date-time-test \
   --filter-expression '#DT BETWEEN :sd AND :ed' \
   --expression-attribute-values '{":sd" : {"S": "2022-07-01"}, ":ed" : {"S": "2022-07-31"} }' \
   --expression-attribute-names '{"#DT" : "DT"}'
   --return-consumed-capacity TOTAL

In the preceding scan, the between operator defined the time frame. 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. You can see the scanned count is 10; which is the total number of items in the DynamoDB table.

2. Retrieve all vehicles traveling faster than 70 mph during the month of July 2022.

   aws dynamodb scan --table-name date-time-test \
   --filter-expression '#DT BETWEEN :sd AND :ed' \
   --expression-attribute-values '{":sd" : {"S": "2022-07-01"}, ":ed" : {"S": "2022-07-31"} }' \
   --expression-attribute-names '{"#DT" : "DT"}'
   --return-consumed-capacity TOTAL

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.

3. Retrieve the details of vehicles traveling faster than 70 mph after July 12, 2022.

   aws dynamodb scan --table-name date-time-test \
   --filter-expression '#DT >= :dt AND #VehicleSpeed >= :vs' \
   --expression-attribute-values '{":dt":{"S":"2022-07-12"}, ":vs" : {"N": "70"}}' \
   --expression-attribute-names '{"#DT" : "DT", "#VehicleSpeed" : "VehicleSpeed"}' \
   --return-consumed-capacity TOTAL

Only one vehicle with number AB-1234-SA was traveling faster than 70 mph after July 12, 2022.

5. Retrieve the details of vehicles traveling faster than 70 mph for the year 2020:

   aws dynamodb scan --table-name date-time-test \
   --filter-expression  'begins_with(#DT, :ts) AND #VehicleSpeed >= :vs' \
   --expression-attribute-values '{":ts" : {"S": "2020"}, ":vs" : {"N" : "70"}}' \
   --expression-attribute-names '{"#DT" : "DT", "#VehicleSpeed" : "VehicleSpeed"}' \
   --return-consumed-capacity TOTAL

The scan for all vehicles in the year 2022 returns four items.

6. Retrieve the details of vehicles traveling faster than 70 mph for the year 2020.

aws dynamodb scan --table-name date-time-test --filter-expression 'begins_with(DT, :ts) AND VehicleSpeed >= :vs' --expression-attribute-values '{":ts" : {"S": "2020"}, ":vs" : {"S" : "70mph"}}' --return-consumed-capacity TOTAL

The scan returns only one item: Vehicle# HJ-6789-SA, which was recorded traveling at 79 mph.

There are several comparison operators available, including EQ, NE, LE, LT, GE, GT, NOT_NULL, NULL, CONTAINS, NOT_CONTAINS, BEGINS_WITH, IN, and BETWEEN.

Working with timestamp data

Some datasets and use- cases benefit from storing timestamp data as a number data type. 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 our use case and based on the access pattern, we use ID as the partition key and the timestamp as the sort 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.

Prepare the timestamp-test table

Complete the following steps to set up your table:

1. 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:

   aws dynamodb create-table --table-name timestamp-test \
   --attribute-definitions AttributeName=camera_id,AttributeType=N AttributeName=ts,AttributeType=N \
   --key-schema AttributeName=camera_id,KeyType=HASH AttributeName=ts,KeyType=RANGE \
   --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

2. Navigate to Tables in the DynamoDB console and verify that timestamp-test has been created.

Figure 3: Use the console to verify that timestamp-test has been created.

3. To store some data in our table, create a file named load1.json with the following content:

   {
       "timestamp-test": [
           {
               "PutRequest": {
                   "Item": {
                       "camera_id": {
                           "N": "1"
                       },
                       "ts": {
                           "N": "1657909382"
                       },
                       "VehicleSpeed": {
                           "S": "72"
                       }  
                   }
               }
           },
           {
               "PutRequest": {
                   "Item": {
                       "camera_id": {
                           "N": "1"
                       },
                       "ts": {
                           "N": "1657909409"
                       },
                       "VehicleSpeed": {
                           "S": "98"
                       }  
                   }
               }
           },
                   {
               "PutRequest": {
                   "Item": {
                       "camera_id": {
                           "N": "1"
                       },
                       "ts": {
                           "N": "1657909412"
                       },
                       "VehicleSpeed": {
                           "S": "64"
                       } 
                   }
               }
           },
                   {
               "PutRequest": {
                   "Item": {
                       "camera_id": {
                           "N": "1"
                       },
                       "ts": {
                           "N": "1657909426"
                       },
                       "VehicleSpeed": {
                           "S": "92"
                       }   
                   }
               }
           },
                   {
               "PutRequest": {
                   "Item": {
                       "camera_id": {
                           "N": "2"
                       },
                       "ts": {
                           "N": "1657909430"
                       },
                       "VehicleSpeed": {
                           "S": "63"
                       }   
                   }
               }
           },
                   {
               "PutRequest": {
                   "Item": {
                       "camera_id": {
                           "N": "2"
                       },
                       "ts": {
                           "N": "1657909466"
                       },
                       "VehicleSpeed": {
                           "S": "76"
                       }   
                   }
               }
           },
                   {
               "PutRequest": {
                   "Item": {
                       "camera_id": {
                           "N": "3"
                       },
                       "ts": {
                           "N": "1657909482"
                       },
                       "VehicleSpeed": {
                           "S": "87"
                       }   
                   }
               }
           }
      ]
   }

4. Run the following command to load the data:

aws dynamodb batch-write-item --request-items file://load1.json

5. 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

Let’s run a series of queries and scans to retrieve timestamp data from timestamp-test.

1. 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.

   aws dynamodb query --table-name timestamp-test  \
   --key-condition-expression '#camera_id = :id AND #ts BETWEEN :sd AND :ed' \
   --expression-attribute-values '{":id" : {"N": "1" }, ":sd" : {"N": "1657909412"}, ":ed" : {"N": "1657909466"} }' \
   --expression-attribute-names '{"#camera_id" : "camera_id" , "#ts" : "ts"}' \
   --return-consumed-capacity TOTAL

The query returns two items from the table between the two timestamps for camera 1.

2. 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.

   aws dynamodb scan --table-name timestamp-test \
   --filter-expression '#camera_id = :id and ts < :ts AND #VehicleSpeed >= :vs' \
   --expression-attribute-values '{":id" : {"N": "1"}, ":ts" : {"N": "1657909482"}, ":vs" : {"N": "70"}}' \
   --expression-attribute-names '{"#camera_id" : "camera_id" , "#VehicleSpeed" : "VehicleSpeed"}' \
   --return-consumed-capacity TOTAL

This returns three items.

3. Retrieve the details of over-speeding vehicles after timestamp 1657909466 which translates to Friday, July 15, 2022 6:24:26 PM

   aws dynamodb scan --table-name timestamp-test --filter-expression '#ts > :ts' \
   --expression-attribute-values '{":ts":{"N":"1657909466"}}' \
   --expression-attribute-names '{"#ts" : "ts"}' \
   --return-consumed-capacity TOTAL

This command returns only one item from the table with Vehicle Speed of 87mph which is for camera_id 3.

Clean up

To avoid unnecessary cost, delete the date-time-test and timestamp-test DynamoDB tables.

• aws dynamodb delete-table --table-name date-time-test
• aws dynamodb delete-table --table-name timestamp-test

Additionally, delete the AWS Cloud9 environment.

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.