MongoDB converting date to string

Question

PROBLEM:

Error when get Y-m-d from "SentTimestamp" : ISODate("2015-12-23T22:20:15Z")

DETAILS :

document :

{
...
"SentTimestamp" : ISODate("2015-12-23T22:20:15Z")
...
}

query :

db.foo.find({}, {$dateToString: {format:"%Y-%m-%d", date:"$SentTimestamp"}})

Error :

Error: error: {
    "$err" : "Can't canonicalize query: BadValue >1 field in obj: { format: \"%Y-%m-%d\", date: \"$SentTimestamp\" }",
    "code" : 17287

Can somebody explain how can I convert date to string, what is wrong above ?

chridam · Accepted Answer · 2020-03-16 07:23:14Z

19

You cannot use the $dateToString operator with projection in the find() method. Instead, use it with the aggregation framework in the $addFields or $project pipeline phase to return documents that have the datetime field converted to string with the desired format, as in the following example:

Using $addFields:

db.foo.aggregate([
    { "$addFields": {
        "sentDateString": { 
            "$dateToString": { 
                "format": "%Y-%m-%d", 
                "date": "$SentTimestamp" 
            } 
        }
    } }
])

or using $project

db.foo.aggregate([
    { "$project": {
        "sentDateString": { 
            "$dateToString": { 
                    "format": "%Y-%m-%d", 
                    "date": "$SentTimestamp" 
            } 
        },
        "otherFields": 1, ....
    } }
])

edited Mar 16, 2020 at 7:23

answered Jan 18, 2016 at 13:40

chridam

104k26 gold badges246 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Varun Over a year ago

@chridam - can we have mix of find and aggregate.. i have to use find query as well.. is that possible?

chridam Over a year ago

Yes, you can include queries with your aggregate operation by using the $match pipeline step. Read more with examples here.

Tenzolinho Over a year ago

Is it there a chance to do this without $project? Because I don't want to project all the fields from an entry..they are about 40 which is a lot.

chridam Over a year ago

@Tenzolinho You can use $addFields like in the above edit

Collectives™ on Stack Overflow

MongoDB converting date to string

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related