CODEWITHSUNDEEP
node.jsmongodb
EtinoXa
EtinoXa

Reputation: 13

Convert Field With Unix TimeStamp to Date (Mongodb/NodeJS)

I am using aggregate in MongoDB to group fields by $year, $month & $dayOfMonth.

Transaction.aggregate(
        [{
            $group: {
                _id: {
                    year : { $year : "$createdAt" },
                    month : { $month :  "$createdAt" },
                    day : { $dayOfMonth :  "$createdAt" },
                },
                totalQuantity: {
                    $sum: "$totalQuantity"
                },
                totalAmount: {
                    $sum: "$totalAmount"
                },
                totalPayment: {
                    $sum: "$totalPayment"
                },
                count: { $sum: 1 }
            }
        }
            ]).then( res =>  console.log(res));

In the above code, I'm using the default $createdAt field but when I try to use $date which has the date in Unix timestamp, it throws an error.

What have I tried?

Transaction.aggregate(
        [{
            $group: {
                _id: {
                    year : { $year : new Date("$date") },
                    month : { $month :  new Date("$date") },
                    day : { $dayOfMonth :  new Date("$date") },
                },
                totalQuantity: {
                    $sum: "$totalQuantity"
                },
                totalAmount: {
                    $sum: "$totalAmount"
                },
                totalPayment: {
                    $sum: "$totalPayment"
                },
                count: { $sum: 1 }
            }
        }
            ]).then( res =>  console.log(res));

But this didn't work as "$date" is passed as a string to the Date constructor. Any workaround this?

Upvotes: 1

Views: 1079

Answers (1)

turivishal
turivishal

Reputation: 36114

You can do it with $toDate, converts unix timestamp milisecond to iso date,

  • $addFields to convert createdAt and replace value
  {
    $addFields: {
      createdAt: { $toDate: "$createdAt" }
    }
  },
  • you can use directly in $group
  {
    $group: {
      _id: {
        year: { $year: "$createdAt" },
        month: { $month: "$createdAt" },
        day: { $dayOfMonth: "$createdAt" }
      }
    }
  }

Playground: https://mongoplayground.net/p/-gWq0YLtLSX

you can convert inside $group also, but this will convert three time and instead of this you can add one time and use in group like above example.

  year: { $year: { $toDate: "$createdAt" } },
  month: { $month: { $toDate: "$createdAt" } },
  day: { $dayOfMonth: { $toDate: "$createdAt" } }

Upvotes: 1

Related Questions