What data type should be use for timestamp in DynamoDB?

dynamodb timestamp java
dynamodb data types
dynamodbtypeconvertedtimestamp
dynamodb date type example
dynamodb auto generated timestamp nodejs
dynamodb timestamp sort key
iso-8601
dynamodb ttl

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

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

How to store Dates or Timestamps in DynamoDB, lets you define when items in a table expire so that they can be automatically deleted from the database. Then, you can add or update items in the table with timestamps in the attribute that you chose. 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 timestamp.

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

Is it possible to save datetime to DynamoDB?, specifies the attributes that make up the primary key of a table, or the key attributes of an index. A KeySchemaElement represents exactly one attribute of the primary key. For example, a simple primary key would be represented by one KeySchemaElement (for the partition key) If you have a small number of items you're updating, you might want to use DynamoDB Streams to batch your increments and reduce the total number of writes to your table. Use ISO-8601 format for timestamps. With DynamoDB, you can use multiple different attribute types, including strings, numbers, and maps.

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

Amazon DynamoDB Update – JSON, Expanded Free Tier, Flexible , Amazon DynamoDB supports the following primitive Java data types and primitive The DynamoDBTypeConverter interface lets you map your own arbitrary data types to a data type that is natively supported by DynamoDB. Terms of Use. For more information about DynamoDB naming rules and the various supported data types, see Naming Rules and Data Types. Empty Binary values are supported by the DynamoDBMapper. Empty String values are supported by AWS SDK for Java 2.0.

Expiring Items Using Time to Live (TTL), Other data types, such as String, aren't supported. TTL attributes must use the epoch time format. For example, the epoch timestamp for October  Use Decimal types instead. One way around this is to use parse_float in json.loads (): 1 2 3 4 5. >>> from decimal import Decimal >>> import json >>> ddb_data = json.loads(json.dumps(data), parse_float=Decimal) >>> ddb_data {u'max': Decimal('747.17'), u'min': Decimal('180.87'), u'timestamp': u'2019-02-05T15:48:27', u'region': u'eu-west-1_dynamodb', u'regionTo': u'us-east-1', u'results': [ {u'seq': 1, u'time': u'747.17'}, {u'seq': 2, u'time': u'215.60'}, {u'seq': 3, u'time': u'230.67'}, {u

AWS::DynamoDB::Table KeySchema, Efficiently store and query chronological data using ISO format and global extending granularity even further by adding timestamps after each date! I could then use this to evaluate the sales of Unisex T-shirts in August! Learn what DynamoDB is, learn about data models in DynamoDB, learn about programmatic access in DynamoDB, and get an idea of whether you should use DynamoDB.

Supported Data Types - Amazon DynamoDB, When I was tasked to delete terabytes of data from AWS DynamoDB tables, I tried below approaches. If you can afford to use new table name, which involves application code change, create brand TTL (Time-To-Live) attribute is the expiry timestamp of DynamoDB table item. TTL attributes must be Number data type. I use the following Python Lambda function to retrieve all my data. The problem I currently face is that the response results are not sorted by timestamp (new to old). def lambda_handler(event, context): response = table.scan() return { 'statusCode': 200, 'body': response['Items'] }

Comments
  • stackoverflow.com/questions/27894393/…
  • We use a number in epoch time/format. We use it for range searches as well along with basically a customer_id
  • String and Number data types can be used. String when storing ISO8601 format, and Number when storing Epoch time. More info here: abhayachauhan.com/2017/12/…
  • The offical doc 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".
  • The Answer which you copied your screenshot from (stackoverflow.com/a/27894543/1357094), answers this Question - and this Question actually duplicates that said Answer's Question in the first place: stackoverflow.com/questions/27894393/…
  • But can you do date range searches when they are stored as String values?
  • @notionquest ya, can you do a date & time range search with String?
  • 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?
  • String and Number data types can be used. String when storing ISO8601 format, and Number when storing Epoch time. More info here: abhayachauhan.com/2017/12/…
  • @roly numbers should also work fine but they're less readable.