Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Format date in mongodb query output on shell

I want to format the date time into an specific format on the mongo shell output

My query

db.getCollection('people').find({
        date: { 
            $gte: ISODate("2017-04-24T14:04:34.447Z") 
        }
    },
    {
        _id: 0,
        age: 0,

    }
);

My output against this query:

/* 1 */
{
    "user_id" : "bcd020",
    "status" : "D",
    "date" : ISODate("2017-04-24T14:04:34.447Z")
}

/* 2 */
{
    "user_id" : "bcd021",
    "status" : "D",
    "date" : ISODate("2017-04-24T14:04:34.447Z")
}

What i want is to format the datetime in the output something like,

/* 1 */
    {
        "user_id" : "bcd020",
        "status" : "D",
        "date" : 2017-04-24 14:04:34
    }

    /* 2 */
    {
        "user_id" : "bcd021",
        "status" : "D",
        "date" : 2017-04-24 14:04:34
    }
like image 615
Abdul Moiz Avatar asked Apr 24 '17 14:04

Abdul Moiz


People also ask

How can I format a date coming from MongoDB?

You can specify a particular date by passing an ISO-8601 date string with a year within the inclusive range 0 through 9999 to the new Date() constructor or the ISODate() function. These functions accept the following formats: new Date("<YYYY-mm-dd>") returns the ISODate with the specified date.

In which format date is stored in MongoDB?

MongoDB will store date and time information using UTC internally, but can easily convert to other timezones at time of retrieval as needed.

Does MongoDB convert date to UTC?

MongoDB stores times in UTC by default, and will convert any local time representations into this form. Applications that must operate or report on some unmodified local time value may store the time zone alongside the UTC timestamp, and compute the original local time in their application logic.

How do I change the date format in aggregation in MongoDB?

If you have documents that store dates as Date objects, but you want to return them in a different format, you can use the $dateToString aggregate pipeline operator. The $dateToString operator converts the Date object to a string, and optionally allows you to specify a format for the resulting output.


1 Answers

Solution is using aggregation pipeline as stated by Veeram in comments section

db.getCollection('people').aggregate([
    {
        $project:{
            datetime: {$dateToString: {format: "%G-%m-%d %H:%M:%S",date: "$datetime"}},
            age : 1
        }
    }
]);
like image 136
Abdul Moiz Avatar answered Sep 25 '22 22:09

Abdul Moiz