150

I am new to DynamoDB. I wish to create a table which using DeviceID as the hash key, Timestamp as my range key and some data.

{ DeviceID: 123, Timestamp: "2016-11-11T17:21:07.5272333Z", X: 12, Y: 35 }

In SQL, we can use datetime type for Timestamp, but in DynamoDB there is none.

  1. What data type should I use? String? Number?
    enter image description here

  2. For the chosen data type, what kind of timestamp format should I write in? ISO format (e.g: 2016-11-11T17:21:07.5272333Z) or epoch time (e.g: 1478943038816)?

  3. I need to search through the table through a range of time, e.g: 1/1/2015 10:00:00am until 31/12/2016 11:00:00pm

cellepo
  • 4,001
  • 2
  • 38
  • 57
Dennis
  • 3,528
  • 4
  • 28
  • 40
  • 1
    http://stackoverflow.com/questions/27894393/is-it-possible-to-save-datetime-to-dynamodb – George Whitaker Nov 12 '16 at 11:28
  • 2
    We use a number in epoch time/format. We use it for range searches as well along with basically a customer_id – George Whitaker Nov 12 '16 at 11:29
  • String and Number data types can be used. String when storing ISO8601 format, and Number when storing Epoch time. More info here: https://www.abhayachauhan.com/2017/12/how-to-store-dates-or-timestamps-in-dynamodb/ – Abhaya Chauhan Dec 21 '17 at 11:49
  • 1
    The [offical doc](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.NamingRulesDataTypes.html) says, "You can use the string data type to represent a date or a timestamp. One way to do this is by using ISO 8601 strings". – Hong Nov 05 '19 at 08:38
  • The Answer which you copied your screenshot from (https://stackoverflow.com/a/27894543/1357094), answers this Question - and this Question actually duplicates that said Answer's Question in the first place: https://stackoverflow.com/questions/27894393/is-it-possible-to-save-datetime-to-dynamodb – cellepo Nov 15 '19 at 21:26
  • Possible duplicate of [Is it possible to save datetime to DynamoDB?](https://stackoverflow.com/questions/27894393/is-it-possible-to-save-datetime-to-dynamodb) – cellepo Nov 15 '19 at 21:27

5 Answers5

138

The String data type should be used for Date or Timestamp.

You can use the String data type to represent a date or a timestamp. 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

DynamoDB Data type for Date or Timestamp

Yes, the Range queries are supported when the date is stored as String. The BETWEEN can be used on FilterExpresssion. I have got the items in the result using the below filter expressions.

FilterExpression without time:-

FilterExpression : 'createdate between :val1 and :val2',
ExpressionAttributeValues : {
        ':hkey' : year_val,
        ':rkey' : title,
        ":val1" : "2010-01-01",
        ":val2" : "2010-12-31"
    }

FilterExpression with time:-

FilterExpression : 'createdate between :val1 and :val2',
    ExpressionAttributeValues : {
        ':hkey' : year_val,
        ':rkey' : title,
        ":val1" : "2010-01-01T00:00:00",
        ":val2" : "2010-12-31T00:00:00"
    }

Database Values:-

Format 1 - with timezone:

{"Item":{"createdate":{"S":"2010-12-21T17:42:34+00:00"},"title":{"S":"The Big New Movie 2010"},"yearkey":{"N":"2010"},"info":{"M":{"rating":{"N":"0"},"plot":{"S":"Nothing happens at all."}}}}}

Format 2 - without timezone:-

{"Item":{"createdate":{"S":"2010-12-21T17:42:34Z"},"title":{"S":"The Big New Movie 2010"},"yearkey":{"N":"2010"},"info":{"M":{"rating":{"N":"0"},"plot":{"S":"Nothing happens at all."}}}}}
Daniel
  • 497
  • 1
  • 7
  • 20
notionquest
  • 37,595
  • 6
  • 111
  • 105
  • But can you do date range searches when they are stored as String values? – Mark B Nov 12 '16 at 15:43
  • @notionquest ya, can you do a date & time range search with String? – Dennis Nov 12 '16 at 16:07
  • @MarkB - Range queries are supported when the date is stored as String. I have updated the answers with example. The Between operator can be used to filter the data. – notionquest Nov 12 '16 at 18:20
  • 2
    A string date can be sorted when stored in ISO 8601 format, because 8601 was designed to sort alphanumerically (within the same timezone). – wprl May 26 '17 at 14:17
  • 97
    I don't see how you arrived at "String data type **should** be used for Date or Timestamp". The docs also say you can also use Number data type for dates via epoch time. Why do you think String **should** be used over Number? – Roly Jul 10 '17 at 01:01
  • This is good but there are now annotations `@DynamoDBTypeConvertedEpochDate` and `@DynamoDBTypeConvertedTimestamp` so it would good to know more about those and see them in action. I think the DynamoDB authors realized that storing java.util.Date as String is annoying as it adds another level of translation to and from the database which should in theory be handled by the database itself it it trivially can be. – fIwJlxSzApHEZIl Aug 30 '17 at 17:48
  • 1
    String and Number data types can be used. String when storing ISO8601 format, and Number when storing Epoch time. More info here: https://www.abhayachauhan.com/2017/12/how-to-store-dates-or-timestamps-in-dynamodb/ – Abhaya Chauhan Dec 13 '17 at 12:40
  • 8
    @roly numbers should also work fine but they're less readable. – Matthew Bonig Sep 10 '18 at 23:06
  • A column named createdate should store just a date, not a datetime. If you want to store a datetime, please use createdatetime (to match your pattern) or better, created_at (and created_on for just dates). – TiggerToo Apr 18 '19 at 15:09
  • @TiggerToo are you referring to something by analogy with "column"? DynamoDB tables have no columns, as DynamoDB is NoSQL. – cellepo Nov 15 '19 at 20:59
  • @Roly is right - and I made another Answer here to make it more clear that String OR Number can be used (since this Answer's acceptance is half deceiving by focusing on String). – cellepo Nov 15 '19 at 21:18
  • This answer is not fully correct, because as per the official documentation you can use Number as well using epoch time. https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.NamingRulesDataTypes.html#HowItWorks.DataTypes.Number You can use the number data type to represent a date or a timestamp. 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 PM UTC on 17 July 2015. – Kuldeep Jain Feb 05 '22 at 02:29
57

Data type depends on your requirements.

You may use String using ISO format or Number using epoch format.

The advantage of ISO format (String) is human readability however DynamoDB does not support Time To Live (TTL) for this format. All filters work such as 'between' and 'range' as explained by notionquest.

Time To Live (TTL) for DynamoDB allows you to define when items in a table expire so that they can be automatically deleted from the database.

The advantage of using epoch format (Number) is that you can use the TTL feature and all filters.

TLDR;

Epoch format (Number type) - Can use Time To Live
ISO format (String type) - Cannot use Time To Live but is more human readable

TheKnightCoder
  • 741
  • 6
  • 4
  • Filter Expressions may be supported by numbers, but range-based Key Conditions would only be supported with strings in ISO format – Rafael Almeida Jun 29 '20 at 12:35
  • @RafaelAlmeida, what do you mean? And what are your sources? – Zachary Ryan Smith Aug 25 '20 at 15:49
  • 1
    @ZacharyRyanSmith I was wrong - indeed, Number attributes do work as sort keys and in Key Conditions in filters. – Rafael Almeida Nov 01 '20 at 00:57
  • But isn't it going to be possible that the number will be a different number of digits, so that when searching you have PK#9999 and then another with PK#10002 And these cannot be filtered by a range query. The ISO format has a fixed length at least so this problem cannot occur... – Worthy7 Jun 27 '23 at 00:55
  • Sunday, 9 September 2001 01:46:40 Is epoch 1000000000 So any dates before this will be 1 less digit. This would make the number verison unqueryable if queried as a string (which most likely you will need since it will be appended to something like PK# etc) – Worthy7 Jun 27 '23 at 00:58
16

for me to be able to filter out results when sending a query request, I used epoch format for DateTime, it is more efficient than using string.

imagine these scenarios: last 31 days, last 24 hours, ... again all is possible using the string format since it also has begins_with operator(please check 3rd example in below link on AWS doc) but numeric values are much more efficient in terms of performance while sorting(comparing) & calculation.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Query.html#Query.KeyConditionExpressions

it is easy to convert date-time to epoch format

Javascript:

var date = new Date();
var epoch = date.getTime();

// converting back to date-time
var initial_date = new Date(epoch);

C#

var date = DateTime.UtcNow;
var epoch = new DateTimeOffset(date).ToUnixTimeSeconds();

// converting back to date-time
var initial_date = DateTimeOffset.FromUnixTimeSeconds(epoch);

Python

import time
epoch = time.time()
 
# converting back to date-time
initial_date = time.gmtime(epoch )
Mohammad Khodabandeh
  • 955
  • 6
  • 17
6

The Number data type OR the String data type

can be used for Date or Timestamp - not just String as the Accepted Answer on this Question incorrectly singles out while ignoring Number.

You can use the number data type to represent a date or a timestamp. 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 PM UTC on 17 July 2015.

For more information, see http://en.wikipedia.org/wiki/Unix_time.

...

You can use the String data type to represent a date or a timestamp. 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

For more information, see http://en.wikipedia.org/wiki/ISO_8601.

DynamoDB Data type for Date or Timestamp

cellepo
  • 4,001
  • 2
  • 38
  • 57
1

In DynamoDB, there are a couple of data types that can be used to store dates. The recommended approach is to use the "String" data type and follow a specific date format such as ISO 8601.

Here are two common formats for storing dates in DynamoDB:

ISO 8601 Format (e.g., "2023-05-23T12:34:56Z"):

This format represents the date and time in UTC. It provides a standard and unambiguous representation of the date and can be easily sorted in chronological order. The "Z" at the end indicates that the date and time are in UTC. You can use this format when you need to perform date comparisons or range queries.

Unix Timestamp (e.g., 1674512096):

Unix timestamps represent the number of seconds that have elapsed since January 1, 1970 (also known as the Unix epoch). Storing dates as Unix timestamps can be useful when you need to perform calculations or convert them to different time zones. However, sorting and querying based on Unix timestamps might require additional processing or indexing.

Additional reasoning

When choosing the appropriate format, consider the specific requirements of your application. If you primarily need to sort, compare, or query dates, the ISO 8601 format is generally more suitable. For example, lets imagine you wanted all of the items which were created in Sept 2023 you could do so easily with ISO 8601, which offers the advantage of human readability:

SELECT * from MyTable WHERE pk = 'x' AND sk begins_with('2023-09')

On the other hand, if you require calculations or time zone conversions, storing dates as Unix timestamps might be preferable. Unix timestamps are also required if you wish to use DynamoDB TTL feature, which requires unix epoch Number types in seconds.

Leeroy Hannigan
  • 11,409
  • 3
  • 14
  • 31