CODEWITHSUNDEEP
mongodb
Silver.Rainbow
Silver.Rainbow

Reputation: 435

String to Date conversion does not retain UTC date

I have document in a collection with a date stored as string which I need to convert to date data type


{
    "billNumber":"B123" 
    "generatedOn" : "2020-07-28T00:00:00Z"
}

when I try to convert this to the date using the following code

db.bills.find
(
    {"billNumber":"B123" }).forEach(function(data)
        { 
            data.generatedOn= ISODate(data.generatedOn)
            db.bills.save(data);
        }   
);

The system saves the date in the date datatype as

2020-07-27T20:00:00-04:00

My questions are

Appreciate the help!

Upvotes: 1

Views: 763

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

If you see 2020-07-27T20:00:00-04:00 then it is because your client application displays the datetime in your local time zone. Which client do you use? There is no "mongo gui tool".

You have the native monogo command shell, there is works as expected:

admin@so> db.bills.insertOne({
...    billNumber: "B123",
...    generatedOn: "2020-07-28T00:00:00Z"
... })
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5f22585fb8ea3f41ce15fd78")
}
admin@so> db.bills.find({ billNumber: "B123" }).forEach(function (data) {
...    data.generatedOn = ISODate(data.generatedOn);
...    db.bills.save(data);
... });
admin@so> db.bills.find({}).pretty()
{
        "_id" : ObjectId("5f22585fb8ea3f41ce15fd78"),
        "billNumber" : "B123",
        "generatedOn" : ISODate("2020-07-28T00:00:00Z")
}

If you like to output the date in specific format or time zone use $dateToString()

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

My understanding is that MongoDB does not store any kind of internal timezone with its dates/timestamps. Rather, it stores all timestamps internally in UTC format. So your input timestamp in EST (New York) is being converted to UTC time (London) by shifting by 4 hours. Mongo would perform this conversion to all incoming timestamps, using the hint of the remote timezone provided by your Mongo driver.

The way to go here therefore is to just assume that internally all timestamps are stored in UTC time. Another option here would be to manually store the original timezone in the JSON. Then, your application may piece together the offset in your code.

Upvotes: 2

Related Questions