28

I'm thinking that UTC time strings like 2011-01-26 21:41:09 +0000 might be okay since they sort correctly if they are used in a view key, but storing the time zone (e.g. 2011-01-26 16:41:09 -0500) would make the document more readable. Converting the date into an epoch integer seem the least appealing from a readability standpoint, but maybe best for performance (or does it make a difference?). What's the recommended practice here?

dreftymac
  • 31,404
  • 26
  • 119
  • 182
dan
  • 43,914
  • 47
  • 153
  • 254

5 Answers5

33

Time is a one-dimensional thing. A timestamp plus timezone is two-dimensional, describing a point in time, and a location. Couch views are one-dimensional (but not the GeoCouch plugin), so storing in a common zone (UTC) is wise.

Probably the most future-proof format is a string that naturally sorts in chronological order. Probably the most convenient such format is what JSON2 outputs:

> a = new Date();
Thu Jan 27 2011 18:40:52 GMT+0700 (ICT)
> JSON.stringify(a)
"2011-01-27T11:40:52.280Z"
JasonSmith
  • 72,674
  • 22
  • 123
  • 149
6

If you're just using the Map side of Map reduce than these suggestions are probably fine. If, however, you want to do a reduce on the results (_count, _stats, _sum), then I'd recommend emitting your dates as arrays so you can use group_level.

For instance, if you emit(doc.date.split('-')) on a date strings formatted like "2011-02-14", then you could return _count's (for instance) per day, month, and year by using group_level=3, 2, and 1 respectively.

You can further filter the data by adding non-date data to the beginning of the key. If you were outputting Twitter names, for instance, your key might look like ["bigbluehat", "2011", "02", "14"] and your reduce could return the total count of all tweets for the user "bigbluehat" as well as stats for that user across day, month, and year.

If you're not using the reduce side of things, then string-based keys are likely fine.

BigBlueHat
  • 2,355
  • 25
  • 30
5

No matter what kind of data storage I use, I typically want a unix timestamp in there as a field, in which I'll include one for the created date, and then an updated field that I can change when the document changes.

I prefer the regular "seconds since epoch" approach rather than "milliseconds since epoch" simply for brevety.

Math.round(new Date().getTime()/1000) does the trick for me.

In terms of readibility, i want to store it as an integer for easy comparisons, and use the front end to display it nicely.

Kristian
  • 21,204
  • 19
  • 101
  • 176
  • 1
    No doubt Unix timestamp is the best way to store time in any system. I can tell you from a background in robotics where timing is important. – msysmilu Oct 31 '14 at 12:45
  • 2
    Unix timestamp is often a good way, but there are many cases where it is not. For example, if you use Unix timestamps on a 32-bit mortgage calculator, you're going to have a bad time when the mortgage runs past 2038, when Unix timestamps run out. – Rich Remer Apr 06 '17 at 15:59
4

You can store your dates how ever you want*, it is how you output them into your views that is important.

*As long as Date.parse() can read it.

There is a good solution here: Sorting Dates in CouchDB Views

Community
  • 1
  • 1
geoffreyd
  • 1,089
  • 6
  • 15
  • +1 however I still recommend the JSON2 format from my answer, because a timestamp might be in a document ID where we have no control of how to output it. (Storing logs is a situation where I've seen a lot of timestamps right in the `_id`.) – JasonSmith Sep 21 '11 at 14:08
4

I like to use milliseconds since last epoch. You can figure this out with:

new Date().valueOf()

You can create a new date from milliseconds with:

var milliseconds = new Date().valueOf();
var date = new Date(milliseconds);

I like to create a view where the timestamp (in milliseconds) is the key b/c sorting is super-easy that way.

Also, I think using integers is more efficient than strings, at least when it comes to working with data outside of CouchDB.

Teddy
  • 18,357
  • 2
  • 30
  • 42