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

Question

I am trying to create aggregation keys in the form of "YYYYMMDD" based on a date field within my documents. Using the $month and $dayOfMonth operators however, I only get numbers returned, without a means of formatting them to leading zeros (and in addition, I can't concatenate numbers).

I'd prefer aggregation over Map/Reduce, due to the blocking nature of the latter. Any ideas?

Answer 1

You basically use the $concat operator to join the strings with a few conditions, as well as $substr to handle the 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 by "day" is to just use a "epoch" value with date math:

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

Any date math operation on two date objects results in the epoch milliseconds as a difference. So use the epoch date as a date object in order to convert. The resulting value is the "day" for the timestamp value, and can be fed back to create a date object when processing your results.

Arguably you could do the same thing in post processing with the $year and $dayOfYear results, as those would also be enough to re-constitute a date object in client processing

Answer 2

While Neil's answer does work, I felt it was rather unsatisfactory; it's hard to read, hard to maintain, and slow. Assuming you're getting the value from a date (as was mentioned in the OP); if you're on mongo 3.0 or later, using $dateToString is the best way, but if you're stuck on an earlier version (like me) I think you're better off simply reading the portion of the string you want from the date field (as these are zero-padded):

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

This works especially well in my case where I just wanted to get <hours>:<minutes> (which is a right pain using @Neil's solution since I want to zero pad two numbers), but this way becomes simply: formattedTime: { $substr: [ "$timestamp", 11, 5 ] }

Of course, I recommend replacing the magic numbers with some named constants for improved readability.