0

I want to create a table using a Timestamp as range key, for sorting my data by date easily.

The epoch format seems the most easy to use -> for example 1437136300

In javascript i'm using new Date().getTime() to get the UTC time.

My question is what's the better data type to save the date in DynamoDB, String or Number?

Found some references of AWS here and here saying it's possible to use Number as type for timestamp because it's the type of all numbers, long included.

Will there be any difference when i query by date? I'm looking for the best practice.

Community
  • 1
  • 1
ronginat
  • 1,910
  • 1
  • 12
  • 23
  • I'm not aware of any best practice here or performance preference, but I generally prefer to store things that are numeric as numbers, not strings. That makes it easier to do calculations on them, such as timestamp plus 500 milliseconds or timestamp minus one day. – jarmod Mar 24 '19 at 17:32
  • thank you @jarmod i taught maybe there is one better way – ronginat Mar 24 '19 at 17:40
  • There isn't an answer [here](https://stackoverflow.com/questions/40561484/what-data-type-should-be-use-for-timestamp-in-dynamodb), he points in his question about saving timestamp as a Number but no one meantioned it in any answer – ronginat Mar 24 '19 at 17:43
  • Oh come on, i obviously checked the internet before posting this question here. Found that post and it didn't help. – ronginat Mar 24 '19 at 17:46
  • Storing as a number will save a few bytes, which depending on your access patterns and overall record size might matter – cementblocks Mar 24 '19 at 17:55
  • thanks @cementblocks . i query my db only by date, this is why it is the range key. Record size is less than 1kb – ronginat Mar 24 '19 at 18:07

1 Answers1

2

As you've pointed out both stings and numbers can be used as sort keys.

Historical References:

@notionquest confirms in a separate thread that the Range queries are supported with strings.

.

Strings:

As far as which one is better I believe you should consider more of your use case. If the data is going to be consumed by another AWS service like Elastic Search or Cloud Watch it would be useful to store it in a form that can be directly consumed by those services. Namely ISO 8601 strings

Example:

2016-02-15

2015-12-21T17:42:34Z

20150311T122706Z

.

Numbers

If the data is going to be consumed by some other graphing or analytic library it might make more sense to have it in a Number format so that it can be directly used without further transformation.

Conclusion

In short, the best practice depends on what is happening downstream. In general, it's preferred to limit data type changes to as few as possible.

Community
  • 1
  • 1
Kerri
  • 301
  • 1
  • 6
  • Thank you for your answer, my database is part of a serverless aws application, that can be accessed via an android app through Api Gateway and aws lambdas. – ronginat Mar 24 '19 at 17:51
  • No problem :) So when the app receives your database information, what does it do with it? Graph it by date? or display it as text? Is the app going to be extracting multiple entries from your table at a time? or only one? – Kerri Mar 24 '19 at 17:56
  • The date is more of a cache related, i fetch only the newest records (from last time fetched) from the Dynamodb to the app. I display the records ordered by date, no fancy graphs – ronginat Mar 24 '19 at 18:04
  • 1
    I would check if your preforms any kind of "sort" function after receiving the data. If so, Number would be the preferred data type. If not, I would recommend ISO 8601 strings simply because they allow you to also store up to millisecond detail while still being readable and have the option for timezone data. (Which might not be useful now but necessary later) – Kerri Mar 24 '19 at 18:12