How to format number 1 to line "01" for aggregation?

Question

I am trying to create aggregation keys in the form of "YYYYMMDD" based on the date field in my documents. However, using the $month and $dayOfMonth , I only get returned numbers without formatting them to leading zeros (and, in addition, I cannot combine numbers).

I would prefer aggregation over Map / Reduce due to its blocking nature. Any ideas?

Answer 1

You basically use the $concat operator to join strings with multiple conditions, as well as $substr to handle conversions:

 "day": { "$concat": [ { "$substr": [ { "$year": "$date" }, 0, 4 ] }, { "$cond": [ { "$lte": [ { "$month": "$date" }, 9 ] }, { "$concat": [ "0", { "$substr": [ { "$month": "$date" }, 0, 2 ] } ]}, { "$substr": [ { "$month": "$date" }, 0, 2 ] } ]}, { "$cond": [ { "$lte": [ { "$dayOfMonth": "$date" }, 9 ] }, { "$concat": [ "0", { "$substr": [ { "$dayOfMonth": "$date" }, 0, 2 ] } ]}, { "$substr": [ { "$dayOfMonth": "$date" }, 0, 2 ] } ]} ] } 

Another approach, if you are aggregating a “day,” is to simply use the “epoch” value with the date math:

 "day": { "$subtract": [ { "$subtract": [ "$date", new Date("1970-01-01") ] }, { "$mod": [ { "$subtract": [ "$date", new Date("1970-01-01") ] }, 1000 * 60 * 60 * 24 ]} ] } 

Any mathematical operation with a date on two date objects results in a difference in milliseconds. Therefore, to convert, use an epoch date as a date object. The resulting value is the "day" for the timestamp value and can be returned to create a date object when processing the results.

Perhaps you could do the same in post-processing with $year and $dayOfYear , as this would also be enough to re-create the date object in client processing

Answer 2

Although Neil's answer does work, I felt that it was rather unsatisfactory; It is hard to read, hard to maintain and slow down. Assuming you get the value from the date (as mentioned in the OP); if you are on mongo 3.0 or later using $ dateToString , this is the best way, but if you are stuck in an earlier version (like me) I think you better just read the part of the string you want from the date field (since they are zero-padded):

 formattedTime: { $concat: [ { $substr: ["$timestamp", 0, 4] }, { $substr: ["$timestamp", 5, 2] }, { $substr: ["$timestamp", 8, 2] } ] } 

This works especially well in my case when I just wanted to get <hours>:<minutes> (which is the right pain using the @Neil solution, since I want zero to skip two numbers), but this path becomes simple: formattedTime: { $substr: [ "$timestamp", 11, 5 ] }

Of course, I recommend replacing magic numbers with some named constants to improve readability.