What's the best way to store datetimes (timestamps) in CouchDB?

Question

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?

Solution 1

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"

Solution 2

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.

Solution 3

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.

Solution 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

Solution 5

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.

View more solutions