What data type should be used for timestamp in DynamoDB?

Datetime Amazon Web-Services Amazon Dynamodb Sqldatatypes

Datetime Problem Overview

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.

What data type should I use? String? Number?
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)?
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

Datetime Solutions

Solution 1 - Datetime

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

Solution 2 - Datetime

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

Solution 3 - Datetime

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);

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 )

Solution 4 - Datetime

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