DynamoDB

About me

DynamoDB

DynamoDB Backups

DynamoDB does not natively support date/timestamp data types.

It sucks – I know. It leads to a lot of confusion. A question I see over and over again is how do you store your dates or timestamps. This is just one example .

The answer is not as straight forward as you’d hope either, because you have two options to assess.

String

Number

It comes down to the format you prefer to store your date or timestamps in.

A key criteria in these options is you want the ability to leverage sorting, and comparison operators in DynamoDB, like Greater Than, Less Than, Between, and Sorting etc.

If you store your dates / timestamps as ISO 8601 format , you should use String.

If you store your dates / timestamps in Epoch format , you should use Number.

DynamoDB collates and compares strings using the bytes of the underlying UTF-8 string encoding. For example, “a” (0x61) is greater than “A” (0x41), and “¿” (0xC2BF) is greater than “z” (0x7A). You can use the string data type to represent a date or a time stamp. One way to do this is by using ISO 8601 strings, as shown in these examples: 2016-02-15 2015-12-21T17:42:34Z< 20150311T122706Z

You can use the string data type to represent a date or a time stamp. One way to do this is by using ISO 8601 strings, as shown in these examples:

2016-02-15

2015-12-21T17:42:34Z<

20150311T122706Z

You can use the number data type to represent a date or a time stamp. One way to do this is by using epoch time—the number of seconds since 00:00:00 UTC on 1 January 1970. For example, the epoch time 1437136300 represents 12:31:40 UTC on 17 July 2015.

String and ISO 8601 is more human readable (easier when dealing with the data directly, ie: Support), but keep in mind, the Time to Live feature uses Epoch time, so if you have any chance of using that feature, I would recommend the Number approach.

HTH

Click to share on Twitter (Opens in new window)

Click to share on LinkedIn (Opens in new window)

Click to share on Reddit (Opens in new window)

Click to share on WhatsApp (Opens in new window)

Click to share on Skype (Opens in new window)

Click to share on Facebook (Opens in new window)

Click to email this to a friend (Opens in new window)

umair · Reply December 29, 2017 at 11:31 PM Can we do a date range query if have stored it in the string format. Abhaya Chauhan · Reply Author December 30, 2017 at 10:47 AM Hi Umair, Good question! If you store the date in ISO 8601 using a string data type, you can perform date range queries in DynamoDB. If you need to perform between two date attributes, you will need to use a FilterExpression: FilterExpression: “start_date BETWEEN :date1 and :date2” Keep in mind though, this will not save you RCUs, as FilterExpressions are executed one the items are returned. Alternatively, if you only need to work with a single date attribute, ideally you can leverage Sort Keys and save RCUs. HTH

Can we do a date range query if have stored it in the string format.

Abhaya Chauhan · Reply Author December 30, 2017 at 10:47 AM Hi Umair, Good question! If you store the date in ISO 8601 using a string data type, you can perform date range queries in DynamoDB. If you need to perform between two date attributes, you will need to use a FilterExpression: FilterExpression: “start_date BETWEEN :date1 and :date2” Keep in mind though, this will not save you RCUs, as FilterExpressions are executed one the items are returned. Alternatively, if you only need to work with a single date attribute, ideally you can leverage Sort Keys and save RCUs. HTH

Hi Umair,

Good question! If you store the date in ISO 8601 using a string data type, you can perform date range queries in DynamoDB.

If you need to perform between two date attributes, you will need to use a FilterExpression:

FilterExpression: “start_date BETWEEN :date1 and :date2”

Keep in mind though, this will not save you RCUs, as FilterExpressions are executed one the items are returned.

Alternatively, if you only need to work with a single date attribute, ideally you can leverage Sort Keys and save RCUs.

Shreya · Reply August 2, 2018 at 10:47 PM Hello Abhaya, I have a table where my Partition Key is some sort of FaceID and I have an attribute “timestamp” which basically is the time when that particular item got inserted or updated. I am writing a Lambda function using Python where I need that function to fetch the item with the latest timestamp. I went through a lot of articles but didn’t find anything helpful with respect to this particular case. Can you please help me with it (I can’t use timestamp as the Key for my table because that table is being updated by another Lambda function which needs FaceID to be the Key for this table). abhayac · Reply Author August 6, 2018 at 12:06 PM Hi Shreya, If the Timestamp is a range key, and you need to find the latest for each FaceId, then you can perform a Query and sort by the Range Key (Timestamp). This is assuming you have formatted the Timestamp correctly. Ideally, a range key should be used to provide the sorting behaviour you are after (finding the latest item). So if you can query within a Partition Key, that would be ideal. The restriction with DynamoDB is that it doesn’t optimise querying across partitions (as it is physically distributed). So the only way to query across partitions is Scans, which is very inefficient. Alternatively, you can record the latest timestamp in another table using DynamoDB Triggers. This could point to the primary key of the latest timestamped item. Remember to use Conditional Writes. Hope that helps. Abhaya Shreya · Reply August 6, 2018 at 8:19 PM Thanks for the response. Are you aware of any documentation pertaining to the second part that you suggested – “Alternatively, you can record the latest timestamp in another table using DynamoDB Triggers. This could point to the primary key of the latest timestamped item. Remember to use Conditional Writes.” abhayac · Reply Author August 7, 2018 at 11:25 AM Unfortunately there isn’t documentation directly which walks through achieving this. You would need to bring together DynamoDB Triggers, with Conditional Writes. The new table would need to record the latest timestamp (Based on your query criteria). Update it if you have a newer timestamp, using Conditional Writes to ensure you don’t have any concurrency issues. https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.ConditionExpressions.html#Expressions.ConditionExpressions.SimpleComparisons Your lambda could just query this table which gives you the latest timestamp, but also the PK of the original item. HTH

Hello Abhaya,

I have a table where my Partition Key is some sort of FaceID and I have an attribute “timestamp” which basically is the time when that particular item got inserted or updated. I am writing a Lambda function using Python where I need that function to fetch the item with the latest timestamp. I went through a lot of articles but didn’t find anything helpful with respect to this particular case. Can you please help me with it (I can’t use timestamp as the Key for my table because that table is being updated by another Lambda function which needs FaceID to be the Key for this table).

abhayac · Reply Author August 6, 2018 at 12:06 PM Hi Shreya, If the Timestamp is a range key, and you need to find the latest for each FaceId, then you can perform a Query and sort by the Range Key (Timestamp). This is assuming you have formatted the Timestamp correctly. Ideally, a range key should be used to provide the sorting behaviour you are after (finding the latest item). So if you can query within a Partition Key, that would be ideal. The restriction with DynamoDB is that it doesn’t optimise querying across partitions (as it is physically distributed). So the only way to query across partitions is Scans, which is very inefficient. Alternatively, you can record the latest timestamp in another table using DynamoDB Triggers. This could point to the primary key of the latest timestamped item. Remember to use Conditional Writes. Hope that helps. Abhaya Shreya · Reply August 6, 2018 at 8:19 PM Thanks for the response. Are you aware of any documentation pertaining to the second part that you suggested – “Alternatively, you can record the latest timestamp in another table using DynamoDB Triggers. This could point to the primary key of the latest timestamped item. Remember to use Conditional Writes.” abhayac · Reply Author August 7, 2018 at 11:25 AM Unfortunately there isn’t documentation directly which walks through achieving this. You would need to bring together DynamoDB Triggers, with Conditional Writes. The new table would need to record the latest timestamp (Based on your query criteria). Update it if you have a newer timestamp, using Conditional Writes to ensure you don’t have any concurrency issues. https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.ConditionExpressions.html#Expressions.ConditionExpressions.SimpleComparisons Your lambda could just query this table which gives you the latest timestamp, but also the PK of the original item. HTH

Hi Shreya, If the Timestamp is a range key, and you need to find the latest for each FaceId, then you can perform a Query and sort by the Range Key (Timestamp). This is assuming you have formatted the Timestamp correctly.

Ideally, a range key should be used to provide the sorting behaviour you are after (finding the latest item). So if you can query within a Partition Key, that would be ideal.

The restriction with DynamoDB is that it doesn’t optimise querying across partitions (as it is physically distributed). So the only way to query across partitions is Scans, which is very inefficient.

Alternatively, you can record the latest timestamp in another table using DynamoDB Triggers. This could point to the primary key of the latest timestamped item. Remember to use Conditional Writes.

Hope that helps.

Abhaya

Shreya · Reply August 6, 2018 at 8:19 PM Thanks for the response. Are you aware of any documentation pertaining to the second part that you suggested – “Alternatively, you can record the latest timestamp in another table using DynamoDB Triggers. This could point to the primary key of the latest timestamped item. Remember to use Conditional Writes.” abhayac · Reply Author August 7, 2018 at 11:25 AM Unfortunately there isn’t documentation directly which walks through achieving this. You would need to bring together DynamoDB Triggers, with Conditional Writes. The new table would need to record the latest timestamp (Based on your query criteria). Update it if you have a newer timestamp, using Conditional Writes to ensure you don’t have any concurrency issues. https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.ConditionExpressions.html#Expressions.ConditionExpressions.SimpleComparisons Your lambda could just query this table which gives you the latest timestamp, but also the PK of the original item. HTH

Thanks for the response. Are you aware of any documentation pertaining to the second part that you suggested – “Alternatively, you can record the latest timestamp in another table using DynamoDB Triggers. This could point to the primary key of the latest timestamped item. Remember to use Conditional Writes.”

abhayac · Reply Author August 7, 2018 at 11:25 AM Unfortunately there isn’t documentation directly which walks through achieving this. You would need to bring together DynamoDB Triggers, with Conditional Writes. The new table would need to record the latest timestamp (Based on your query criteria). Update it if you have a newer timestamp, using Conditional Writes to ensure you don’t have any concurrency issues. https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.ConditionExpressions.html#Expressions.ConditionExpressions.SimpleComparisons Your lambda could just query this table which gives you the latest timestamp, but also the PK of the original item. HTH

Unfortunately there isn’t documentation directly which walks through achieving this. You would need to bring together DynamoDB Triggers, with Conditional Writes.

The new table would need to record the latest timestamp (Based on your query criteria). Update it if you have a newer timestamp, using Conditional Writes to ensure you don’t have any concurrency issues.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.ConditionExpressions.html#Expressions.ConditionExpressions.SimpleComparisons

Your lambda could just query this table which gives you the latest timestamp, but also the PK of the original item.

Ricardo Silva · Reply September 8, 2018 at 11:51 AM Hi, I’m designing a scheduling system. I plan to create the data layer with DynamoDB, would you recommend that option or would you choose a relational database? I think about the handling and calculation of dates. Greetings and thanks for the post. Michael Fuery · Reply September 11, 2018 at 9:29 AM Use relational if you ever want to query by more than one or two attributes. As I understand, Dynamo can only filter data based on an index. As this article points out, Dynamo only has primary and secondary indices. Michael Fuery · Reply September 11, 2018 at 9:30 AM Btw For a scheduling system, I’d definitely use a relational database! abhayac · Reply Author September 11, 2018 at 9:42 PM Hi Ricardo, Thanks for the post. I would echo Michael’s comments. Keeping in mind, I don’t know your requirements / use cases for the scheduling system, but in general, you would be very limited in terms of your querying when working with DynamoDB. A relational database would give you a lot more flexibility. Abhaya Ricardo Silva · Reply September 14, 2018 at 9:35 AM Hello Michael and Abhaya, you have helped me a lot, I was not too clear about that restriction of DynamoDB in terms of searching for elements by their indexes. I think we will modify the approach of the structure by an RDS Instance. Thank you very much! Greets!

Hi, I’m designing a scheduling system. I plan to create the data layer with DynamoDB, would you recommend that option or would you choose a relational database? I think about the handling and calculation of dates. Greetings and thanks for the post.

Michael Fuery · Reply September 11, 2018 at 9:29 AM Use relational if you ever want to query by more than one or two attributes. As I understand, Dynamo can only filter data based on an index. As this article points out, Dynamo only has primary and secondary indices.

Use relational if you ever want to query by more than one or two attributes. As I understand, Dynamo can only filter data based on an index. As this article points out, Dynamo only has primary and secondary indices.

Michael Fuery · Reply September 11, 2018 at 9:30 AM Btw For a scheduling system, I’d definitely use a relational database!

Btw For a scheduling system, I’d definitely use a relational database!

abhayac · Reply Author September 11, 2018 at 9:42 PM Hi Ricardo, Thanks for the post. I would echo Michael’s comments. Keeping in mind, I don’t know your requirements / use cases for the scheduling system, but in general, you would be very limited in terms of your querying when working with DynamoDB. A relational database would give you a lot more flexibility. Abhaya Ricardo Silva · Reply September 14, 2018 at 9:35 AM Hello Michael and Abhaya, you have helped me a lot, I was not too clear about that restriction of DynamoDB in terms of searching for elements by their indexes. I think we will modify the approach of the structure by an RDS Instance. Thank you very much! Greets!

Hi Ricardo, Thanks for the post. I would echo Michael’s comments. Keeping in mind, I don’t know your requirements / use cases for the scheduling system, but in general, you would be very limited in terms of your querying when working with DynamoDB. A relational database would give you a lot more flexibility.

Ricardo Silva · Reply September 14, 2018 at 9:35 AM Hello Michael and Abhaya, you have helped me a lot, I was not too clear about that restriction of DynamoDB in terms of searching for elements by their indexes. I think we will modify the approach of the structure by an RDS Instance. Thank you very much! Greets!

Hello Michael and Abhaya, you have helped me a lot, I was not too clear about that restriction of DynamoDB in terms of searching for elements by their indexes. I think we will modify the approach of the structure by an RDS Instance. Thank you very much!

Greets!

Jayson Suyat · Reply October 12, 2018 at 12:24 PM Hi Abhaya, Im having difficulty querying through DynamoDB I have this column start_date and end_date and I want to query like start_date > “date” and end_date < "date" can you work through me how can I achieve this. Thanks

Hi Abhaya, Im having difficulty querying through DynamoDB I have this column start_date and end_date and I want to query like start_date > “date” and end_date < "date" can you work through me how can I achieve this. Thanks

Ioannis Koustoudis · Reply November 17, 2018 at 12:17 AM Hi Abhaya, One thing I was aware about Dynamodb is the fact that you can’t query over a range on the Primary key! The only option is to “match” a specific value for the primary and search on a range of the sort key. I faced a problem of retrieving the last 7 days-entries of a table and the only way to achieve it was to change the table and assign as primary key something else than the date. Thanks

Hi Abhaya, One thing I was aware about Dynamodb is the fact that you can’t query over a range on the Primary key! The only option is to “match” a specific value for the primary and search on a range of the sort key. I faced a problem of retrieving the last 7 days-entries of a table and the only way to achieve it was to change the table and assign as primary key something else than the date. Thanks

Paul · Reply June 13, 2019 at 3:36 PM Hello Abhaya Chauhan, i have a column with DateTime for both Start and EndDate and now i want to query something like EndDate > CurrentDate ( need to consider only date part, ignore Timestamp).. What you suggest?

Hello Abhaya Chauhan, i have a column with DateTime for both Start and EndDate and now i want to query something like EndDate > CurrentDate ( need to consider only date part, ignore Timestamp).. What you suggest?

AWS Specialist, passionate about DynamoDB and the Serverless movement.

Currently focusing on helping SaaS products leverage technology to innovate, scale and be market leaders.

Introduction to CloudFormation

Getting Started with DynamoDB

DynamoDB in Production

Intro to CloudFormation Course

Using DynamoDB in Production – New Course

DynamoDB: Monitoring Capacity and Throttling

Pluralsight Course: Getting Started with DynamoDB

Partition Throttling: How to detect hot Partitions / Keys

AWS

Lambda

Pluralsight

Presentations

Rants

Uncategorized

Type and press Enter to search