datetime - What data type should be use for timestamp in DynamoDB?

Question

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?
   

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

Answer 1

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."}}}}}