General Problem of Dealing with "local dates"
So there is a short answer to this and a long answer as well. The basic case is that instead of using any of the "date aggregation operators" you instead rather want to and "need to" actually "do the math" on the date objects instead. The primary thing here is to adjust the values by the offset from UTC for the given local timezone and then "round" to the required interval.
The "much longer answer" and also the main problem to consider involves that dates are often subject to "Daylight Savings Time" changes in the offset from UTC at different times of the year. So this means that when converting to "local time" for such aggregation purposes, you really should consider where the boundaries for such changes exist.
There is also another consideration, being that no matter what you do to "aggregate" at a given interval, the output values "should" at least initially come out as UTC. This is good practice since display to "locale" really is a "client function", and as later described, the client interfaces will commonly have a way of displaying in the present locale which will be based on the premise that it was in fact fed data as UTC.
Determining Locale Offset and Daylight Savings
This is generally the main problem that needs to be solved. The general math for "rounding" a date to an interval is the simple part, but there is no real math you can apply to knowing when such boundaries apply, and the rules change in every locale and often every year.
So this is where a "library" comes in, and the best option here in the authors opinion for a JavaScript platform is moment-timezone, which is basically a "superset" of moment.js including all the important "timezeone" features we want to use.
Moment Timezone basically defines such a structure for each locale timezone as:
{
name : 'America/Los_Angeles', // the unique identifier
abbrs : ['PDT', 'PST'], // the abbreviations
untils : [1414918800000, 1425808800000], // the timestamps in milliseconds
offsets : [420, 480] // the offsets in minutes
}
Where of course the objects are much larger with respect to the untils and offsets properties actually recorded. But that is the data you need to access in order to see if there is actually a change in the offset for a zone given daylight savings changes.
This block of the later code listing is what we basically use to determine given a start and end value for a range, which daylight savings boundaries are crossed, if any:
const zone = moment.tz.zone(locale);
if ( zone.hasOwnProperty('untils') ) {
let between = zone.untils.filter( u =>
u >= start.valueOf() && u < end.valueOf()
);
if ( between.length > 0 )
branches = between
.map( d => moment.tz(d, locale) )
.reduce((acc,curr,i,arr) =>
acc.concat(
( i === 0 )
? [{ start, end: curr }] : [{ start: acc[i-1].end, end: curr }],
( i === arr.length-1 ) ? [{ start: curr, end }] : []
)
,[]);
}
Looking at the whole of 2017 for the Australia/Sydney locale the output of this would be:
[
{
"start": "2016-12-31T13:00:00.000Z", // Interval is +11 hours here
"end": "2017-04-01T16:00:00.000Z"
},
{
"start": "2017-04-01T16:00:00.000Z", // Changes to +10 hours here
"end": "2017-09-30T16:00:00.000Z"
},
{
"start": "2017-09-30T16:00:00.000Z", // Changes back to +11 hours here
"end": "2017-12-31T13:00:00.000Z"
}
]
Which basically reveals that between the first sequence of dates the offset would be +11 hours then changes to +10 hours between the dates in the second sequence and then switches back to +11 hours for the interval covering to the end of the year and the specified range.
This logic then needs to be translated into a structure that will be understood by MongoDB as part of an aggregation pipeline.
Applying the Math
The mathematical principle here for aggregating to any "rounded date interval" essentially relies on using the milliseconds value of the represented date which is "rounded" down to the nearest number representing the "interval" required.
You essentially do this by finding the "modulo" or "remainder" of the current value applied to the required interval. Then you "subtract" that remainder from the current value which returns a value at the nearest interval.
For example, given the current date:
var d = new Date("2017-07-14T01:28:34.931Z"); // toValue() is 1499995714931 millis
// 1000 millseconds * 60 seconds * 60 minutes = 1 hour or 3600000 millis
var v = d.valueOf() - ( d.valueOf() % ( 1000 * 60 * 60 ) );
// v equals 1499994000000 millis or as a date
new Date(1499994000000);
ISODate("2017-07-14T01:00:00Z")
// which removed the 28 minutes and change to nearest 1 hour interval
This is the general math we also need to apply in the aggregation pipeline using the $subtract and $mod operations, which are the aggregation expressions used for the same math operations shown above.
The general structure of the aggregation pipeline is then:
let pipeline = [
{ "$match": {
"createdAt": { "$gte": start.toDate(), "$lt": end.toDate() }
}},
{ "$group": {
"_id": {
"$add": [
{ "$subtract": [
{ "$subtract": [
{ "$subtract": [ "$createdAt", new Date(0) ] },
switchOffset(start,end,"$createdAt",false)
]},
{ "$mod": [
{ "$subtract": [
{ "$subtract": [ "$createdAt", new Date(0) ] },
switchOffset(start,end,"$createdAt",false)
]},
interval
]}
]},
new Date(0)
]
},
"amount": { "$sum": "$amount" }
}},
{ "$addFields": {
"_id": {
"$add": [
"$_id", switchOffset(start,end,"$_id",true)
]
}
}},
{ "$sort": { "_id": 1 } }
];
The main parts here you need to understand is the conversion from a Date object as stored in MongoDB to Numeric representing the internal timestamp value. We need the "numeric" form, and to do this is a trick of math where we subtract one BSON Date from another which yields the numeric difference between them. This is exactly what this statement does:
{ "$subtract": [ "$createdAt", new Date(0) ] }
Now we have a numeric value to deal with, we can apply the modulo and subtract that from the numeric representation of the date in order to "round" it. So the "straight" representation of this is like:
{ "$subtract": [
{ "$subtract": [ "$createdAt", new Date(0) ] },
{ "$mod": [
{ "$subtract": [ "$createdAt", new Date(0) ] },
( 1000 * 60 * 60 * 24 ) // 24 hours
]}
]}
Which mirrors the same JavaScript math approach as shown earlier but applied to the actual document values in the aggregation pipeline. You will also note the other "trick" there where we apply an $add operation with another representation of a BSON date as of epoch ( or 0 milliseconds ) where the "addition" of a BSON Date to a "numeric" value, returns a "BSON Date" representing the milliseconds it was given as input.
Of course the other consideration in the listed code it the actual "offset" from UTC which is adjusting the numeric values in order to ensure the "rounding" takes place for the present timezone. This is implemented in a function based on the earlier description of finding where the different offsets occur, and returns a format as usable in an aggregation pipeline expression by comparing the input dates and returning the correct offset.
With the full expansion of all the details, including the generation of handling those different "Daylight Savings" time offsets would then be like:
[
{
"$match": {
"createdAt": {
"$gte": "2016-12-31T13:00:00.000Z",
"$lt": "2017-12-31T13:00:00.000Z"
}
}
},
{
"$group": {
"_id": {
"$add": [
{
"$subtract": [
{
"$subtract": [
{
"$subtract": [
"$createdAt",
"1970-01-01T00:00:00.000Z"
]
},
{
"$switch": {
"branches": [
{
"case": {
"$and": [
{
"$gte": [
"$createdAt",
"2016-12-31T13:00:00.000Z"
]
},
{
"$lt": [
"$createdAt",
"2017-04-01T16:00:00.000Z"
]
}
]
},
"then": -39600000
},
{
"case": {
"$and": [
{
"$gte": [
"$createdAt",
"2017-04-01T16:00:00.000Z"
]
},
{
"$lt": [
"$createdAt",
"2017-09-30T16:00:00.000Z"
]
}
]
},
"then": -36000000
},
{
"case": {
"$and": [
{
"$gte": [
"$createdAt",
"2017-09-30T16:00:00.000Z"
]
},
{
"$lt": [
"$createdAt",
"2017-12-31T13:00:00.000Z"
]
}
]
},
"then": -39600000
}
]
}
}
