How to generate time series aggregations in MongoDB using Unix / Epoch timestamps (week over week, year over year, etc. tutorial and example)

Screen Shot 2015-10-12 at 3.19.44 PM
ECMAScript5 introduced, a method that returns the milliseconds elapsed since 1 January 1970 00:00:00 UTC up until now as a Number. is the fastest way to record a timestamp in JavaScript[1], [2] and as such, usage will continue to increase as developers become educated in speed improvements.

While speed improvements are important, storing Unix / Epoch timestamps in MongoDB come with a quirk; MongoDB only support BSON native date objects. What this means as a developer is that the Date Aggregation Operators such as $dayOfWeek and $dayOfYear are unusable natively. This poses a challenge when trying to obtain time series reports such as week over week reports, month over month reports, year over year reports, etc. There are two tricks one can use to solving this problem.

Sample data

For the proceeding examples, we will assume our database has a collection called shipments and documents that look like this:

Approach 1

The first approach can be achieved by using an addition aggregation to convert the timestamp into a BSON object in memory then utilize MongoDB’s Date Aggregation Operators to obtain the desired result. Below is an example of this approach.

MongoDB query

MongoDB result

Approach 2

Approach 1 above works fine and is probably the preferred method, however in many production type situations the query is stringified by a framework or application prior to submitting / sending the query. If this were true in the above example, { $add : [new Date(0), "$sentAt"] } would have been converted to { $add : ["Wed Dec 31 1969 16:00:00 GMT-0800 (PST)", "$sentAt"] } prior to submission, causing MongoDB to return the error message "errmsg" : "exception: $add only supports numeric or date types, not String".

A workaround to the above problem can be had using the n - (n % ) modulo trick. This equation allows us to round any number to the nearest interval. For example, if we had a list of numbers such as 105.2, 251 and 170.1 and we wanted to round to the nearest hundreth, performing the above operations on each number using the interval of 100 would result in 100, 200, and 100. We can extrapolate this logic and apply it to days, weeks, months, quarters, years, etc. Note, one limitation of this method is that it does not honor daylight savings time.

Now, let’s apply Approach 2 to the same shipments example. For reference, one year is equivalent to 31,556,908,800 milliseconds.

MongoDB query

MongoDB result