I have a couple of items of which one field is a UTC-based unix timestamp multiplied by 1000 in order to include milliseconds while keeping it a long (integer) value.
{
"title" : "Merkel 'explains' refugee convention to Trump in phone call",
"iso" : "2017-01-31T04:03:53.807+0000",
"id" : NumberLong(1485835433807)
}
{
"title" : "NASA to Explore an Asteroid Containing Enough Mineral Wealth to Collapse the World Economy",
"iso" : "2017-01-30T23:20:27.327+0000",
"id" : NumberLong(1485818427327)
}
{
"title" : "IMGKit: Python library of HTML to IMG wrapper",
"iso" : "2017-01-30T23:15:39.488+0000",
"id" : NumberLong(1485818139488)
}
the iso field is just a text string to ease debugging, it has no other purpose.
I intend to use the method described in https://stackoverflow.com/a/26550803/277267 to resample the items, to create a summary of items per day, initially just a count if items per day.
The problem is that the timestamp (the "id" field) can't really be used to archieve this, because of the UTC offset. Depending on the location of the user (or the local insertion time, ie 00:30 monday local time vs 23:30 sunday UTC time, if the timezone is +1h) an item would belong either to one day or the other, so the field lacks this information.
Assuming I just want to add an offset to the "id" field, ie by 3600000, which is one hour expressed in milliseconds, before starting to resample the data based on the "id" field, how can I archieve this in the aggregation pipeline?
Is there a way to have a first stage which takes the "id" field value, add 3600000 to that value and store it into an "id_offsetted" field, on which I can then execute the next stages?
Version before 3.4
{$project: {
"title" : 1,
"iso" : 1
"id" : 1,
"id_offsetted" : {$add: ["$id", 3600000]}
} }
Version 3.4 onwards
{$addFields: {
"id_offsetted" : {$add: ["$id", 3600000]}
} }