Mongodb has a record table with a field DateTime in the table. The type is Date. I want to find out which dates have records
Because the time in the database is UTC time, which is 8 hours different from China time, I will add 8 hours before grouping. SQL is as follows
db.getCollection('discerns').aggregate([
{
$project: {
'date': {
$add:['$DateTime',28800000]
}
}
},{
$project: {
'year': {
$year: '$date'
},
'month': {
$month: '$date'
},
'day': {
$dayOfMonth: '$date'
}
}
},{
$group: {
_id: {
year:'$year',
month: '$month',
day:'$day'
}
}
},{
$sort: {'_id': -1}
}
]);
I wonder if it is possible to optimize two $projects into one $ project. is this possible?
That is, whether it can be done, first add 8 hours to the time, and then get the year, month and day of the calculated time, all of which are written in a $project
db.getCollection('discerns').aggregate([{ $project: { 'year': { $year: { $add: ['$DateTime', 28800000] } }, 'month': { $month: { $add: ['$DateTime', 28800000] } }, 'day': { $dayOfMonth: { $add: ['$DateTime', 28800000] } } } }])
Simply write it together. Of course, this will count three times.
$add: ['$DateTime', 28800000]
, for obsessive-compulsive disorder patients is difficult to accept, so we can use variable definition method to play again:db.getCollection('discerns').aggregate([{ $project: { dateParts: { $let: { vars: { date: { $add: ['$DateTime', 28800000] } }, in: { year: { $year: "$$date" }, month: { $month: "$$date" }, day: { $dayOfMonth: "$$date" } } } } } }])
About
$let
Please refer to the documentation for the usage of:$letThe results thus obtained are actually nested one layer, which is not the same as the initial results, but for the latter ones
$group
No big deal, I believe you can handle it.