I am new to MongoDB and I am stuck on the String to Date conversion. In the db the date item is stored in String type as "date":"2015-06-16T17:50:30.081Z"

I want to group the docs by date and calculate the sum of each day so I have to extract year, month and day from the date string and wipe off the hrs, mins and seconds. I have tried multiple way but they either return a date type of 1970-01-01 or the current date.

Moreover, I want to convert the following mongo query into python code, which get me the same problem, I am not able to call a javascript function in python, and the datetime can not parse the mongo syntax $date either.

I have tried:

new Date("2015-06-16T17:50:30.081Z")
new Date(Date.parse("2015-06-16T17:50:30.081Z"))
etc...

I am perfectly find if the string is given in Javascript or in Python, I know more than one way to parse it. However I have no idea about how to do it in MongoDB query.

  db.collection.aggregate([
                    {
                        //somthing
                    },
                    { 
                        '$group':{
                            '_id':{ 
                                'month':  (new Date(Date.parse('$approTime'))).getMonth(), 
                                'day': (new Date(Date.parse('$approTime'))).getDate(), 
                                'year':  (new Date(Date.parse('$approTime'))).getFullYear(),
                                'countries':'$countries'
                            },
                             'count': {'$sum':1}

                    }
                }
])

I am new to MongoDB and I am stuck on the String to Date conversion. In the db the date item is stored in String type as "date":"2015-06-16T17:50:30.081Z"

I want to group the docs by date and calculate the sum of each day so I have to extract year, month and day from the date string and wipe off the hrs, mins and seconds. I have tried multiple way but they either return a date type of 1970-01-01 or the current date.

Moreover, I want to convert the following mongo query into python code, which get me the same problem, I am not able to call a javascript function in python, and the datetime can not parse the mongo syntax $date either.

I have tried:

new Date("2015-06-16T17:50:30.081Z")
new Date(Date.parse("2015-06-16T17:50:30.081Z"))
etc...

I am perfectly find if the string is given in Javascript or in Python, I know more than one way to parse it. However I have no idea about how to do it in MongoDB query.

  db.collection.aggregate([
                    {
                        //somthing
                    },
                    { 
                        '$group':{
                            '_id':{ 
                                'month':  (new Date(Date.parse('$approTime'))).getMonth(), 
                                'day': (new Date(Date.parse('$approTime'))).getDate(), 
                                'year':  (new Date(Date.parse('$approTime'))).getFullYear(),
                                'countries':'$countries'
                            },
                             'count': {'$sum':1}

                    }
                }
])

• javascript
• python
• mongodb
• date

• 2 Why are you storing date as a String? – Buzz Moschetti Commented Jun 16, 2015 at 21:12
• @Buzz Someone else did, I have to get the data without changing the production environment at this moment – pledez Commented Jun 17, 2015 at 3:38
• @BuzzMoschetti The reason I store it as a string is because I don't want to be converted to UTC time. I need to group on week numbers, so when I do that in UTC, (I am AEST (sydney) time) something that was on Sptember 1st at 6:00am would be August 31 somewhere in the afternoon. That could just mean it is counted the previous week number. I am stuck with Mongo 2.6 right now, so there are not many options beside extracting the AEST Weeknumber in the import and store it separately – Mattijs Commented Dec 13, 2015 at 4:37

2 Answers 2

If you can be assured of the format of the input date string AND you are just trying to get a count of unique YYYYMMDD, then just $project the substring and group on it:

var data = [
        { "name": "buzz", "d1": "2015-06-16T17:50:30.081Z"},
        { "name": "matt", "d1": "2018-06-16T17:50:30.081Z"},
        { "name": "bob", "d1": "2018-06-16T17:50:30.081Z"},
        { "name": "corn", "d1": "2019-06-16T17:50:30.081Z"},
      ];

db.foo.drop();
db.foo.insert(data);

db.foo.aggregate([
     { "$project": {
         "xd": { "$substr": [ "$d1", 0, 10 ]}
         }
 },
 { "$group": {
             "_id": "$xd",
             "n": {$sum: 1}
         }
     }
              ]);

{ "_id" : "2019-06-16", "n" : 1 }
{ "_id" : "2018-06-16", "n" : 2 }
{ "_id" : "2015-06-16", "n" : 1 }

Starting in Mongo 4.0, you can use "$toDate" to convert a string to a date:

// { name: "buzz", d1: "2015-06-16T17:50:30.081Z" }
// { name: "matt", d1: "2018-06-16T17:50:30.081Z" }
// { name: "bob",  d1: "2018-06-16T17:50:30.081Z" }
// { name: "corn", d1: "2019-06-16T17:50:30.081Z" }
db.collection.aggregate(
  { $group: {
    _id: { $dateToString: { date: { $toDate: "$d1" }, format: "%Y-%m-%d" } },
    n: { $sum: 1 }
  }}
)
// { _id: "2015-06-16", n: 1 }
// { _id: "2018-06-16", n: 2 }
// { _id: "2019-06-16", n: 1 }

Within the group stage, this:

• first converts strings (such as "2015-06-16T17:50:30.081Z") to date objects (ISODate("2015-06-16T17:50:30.081Z")) using the "$toDate" operator.

• then converts the converted date (such as ISODate("2015-06-16T17:50:30.081Z")) back to string ("2015-06-16") but this time with this format "%Y-%m-%d", using the $dateToString operator.