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?

Answer 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" 

Answer 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.