How-To Work With Timestamps in DynamoDB
Written by Charlie Fish
Published on March 9th, 2022
Time to 10x your DynamoDB productivity with Dynobase [learn more]
Introduction
Working with time in databases is a very common task. In this guide, we will be looking at how to work with timestamps in DynamoDB.
Representing Timestamps in DynamoDB
There are multiple ways to represent a timestamp in DynamoDB. Probably the most common is to use a Number type to represent the timestamp with the value as a Unix timestamp (seconds or milliseconds). Additionally, you can store the timestamp as a String type with the value as an ISO 8601 formatted string.
Personally, I believe storing timestamps as a Number type has some major advantages. Mainly it makes it easier to query, filter, sort, and perform mathematical operations on the timestamp. However, it is also less human-readable when viewing the raw data from DynamoDB. However, this downside can easily be mitigated by building a script to parse the data and convert it to a human-readable string.
Timestamp Precision
It is also important to consider how precise you need your timestamps to be. For example, do you only need to know the year a specific item was created at? Or do you need to know millisecond-level precision for your item? This choice will be heavily application dependent, and you will have to weigh the potential benefits and drawbacks of different precision levels.
How-To Store Timestamp Upon Creation in DynamoDB
To store a value with the updated_at timestamp to be 2022-01-01, you could run the following putItem command:
This will set the updated_at timestamp to 2022-01-31 for the item with the uuid of 522fb5a5-6bf0-4c5f-821d-6c061f54cebe.
Querying DynamoDB Based on Timestamps
So now we have a solid idea of how to store timestamps in DynamoDB. Let's look at how to query based on that timestamp. In some cases, you may not even need to query based on the timestamp. If it's just a standard attribute for a value you want to display to a user when they view a record, you may not need to query based on the timestamp. So this is all very application-dependent. As with all databases, it's important to write a full list of all the ways you want to query your data.
For the most part, you won't want to use your timestamp as a Partition Key. This is due to the fact that it's best when your partitions are spread efficiently across your data. Storing a Unix timestamp as a partition means you will have a lot of partitions with very few items in each partition. However, it might make sense to use an index with the partition key set to the time if you are storing the timestamp with a lower precision, such as just the year and month. Of course, there are some application-level considerations to think about when doing that, such as the number of items and separation between partitions.
Likely the most common way to query time data is to set it as a Sort Key on your index. This will allow you to write efficient queries using the mathematical operators (greater than, less than, etc.) and the between operator.
Below you will find a few examples of querying/sorting your DynamoDB items based on your timestamps.
For all of the examples shown below, we will have the following format:
- Partition key -
pk - Sort key -
updated_at
We are going to assume that the pk format is type_userid. For example, orders for userid A would have a partition key of order_A.
Querying Between Two Timestamps
To query all orders by user A that were updated between 2022-01-01 and 2022-01-31, you would do the following:
This will query all orders updated by user A between 2022-01-01 and 2022-01-31.
Query Latest Timestamp
Now let's say you wanted to get the latest order from user B. You could do the following:
This will return the last order from user B.
Sort/Order By Timestamp
Similarly, you could sort all orders by timestamp by doing the same command as above but without the Limit property.
This will sort your items from newest to oldest. If you wanted to sort from oldest to newest, you would set the ScanIndexForward property to true.
How-To Update Timestamp in DynamoDB
Let's say you want to update the updated_at timestamp to be 2022-01-31, you could run the following updateItem command:
This will set the updated_at timestamp to 2022-01-31 for the item with the uuid of 522fb5a5-6bf0-4c5f-821d-6c061f54cebe.
Time to Live in DynamoDB
Although not the primary focus of this article, it is important to also look at one more key feature of DynamoDB: Time to Live (TTL). At a very high level, this feature allows you to delete old/expired/stale data from your database to save on storage costs and reduce the size of your DynamoDB Table.
Luckily, Dynobase has another great tutorial and guide that dives deep into this feature: DynamoDB TTL (Time to Live) - How to Use It Effectively. I'd highly encourage you to check out that guide if you have data that needs to be deleted from your database based on an expiration date.