How to update collection and increment hours for ISO date

Question

I have an ISO date in my collection documents.

"start" : ISODate("2015-07-25T17:35:00Z"),
"end" : ISODate("2015-09-01T23:59:00Z"),

Currently they are in GMT +0, i need them to be GMT +8. Therefore i need to add 8 hours to the existing field. How do i do this via a mongodb query?

Advice appreciated.

Updated Code Snippet

var offset = 8,
bulk = db.collection.initializeUnorderedBulkOp(),
count = 0;

db.collection.find().forEach(doc) {
bulk.find({ "_id": doc._id }).updateOne({
   "$set": { “startDateTime": new Date(
       doc.startDateTime.valueOf() + ( 1000 * 60 * 60 * offset )
   ) }
});
count++;

if ( count % 1000 == 0 ) {
    bulk.execute();
    bulk = db.collection.initializeUnorderedBulkOp();
}
});

if ( count % 1000 !=0 )
    bulk.execute();

You shouldn't do that. Leave them as UTC and work out the difference in your client API. Saving as UTC is the correct thing to do. That's why it's the chosen format. — Blakes Seven, Aug 01 '15 at 10:29

score 4 · Accepted Answer · edited May 23 '17 at 12:06

I aggree wholeheartedly with the answer provided by Ewan here in that you really should keep all times in a database in UTC. And all the sentiments are correct there. Only really adding to this with practical examples.

As a working example, Let's say I have two people using the data, one in New York and one in Sydney, being UTC-5 and UTC+10 respectively. Now consider the following data:

{ "date": ISODate("2015-08-01T04:40:03.389Z") }

Based on that, this is the time the actual "event" takes place. To the perspective of the user in Sydney the event takes place on the 1st August as a whole day where as to the person in New York it is still occuring on the 31st July.

If however I construct a "localized" time for Sydney as follows, the UTC consideration is still correct:

 new Date("2015/08/01")
 ISODate("2015-07-31T14:00:00Z")

This enforces the time difference like it should by converting from the local timezone to UTC. Therefore a localized date will select the correct values in UTC. So the Sydney user perpective of the start of the 1st August includes all times from 2pm on 31st July and similarly adjusted to the end date of a range selection. With data in UTC, this assertion from the client end it correct, and to their perpective the selected data was in the expected range.

In the case where you were "aggregating" results for a given day, then you build in the "time difference" math into the expression. So for UTC+10 you would do:

var offset = 10;

db.collection.aggregate([
    { "$group": {
        "_id": {
            "$subtract": [
                { "$add": [
                    {"$subtract": [ "$date", new Date(0)]},
                    offset * 1000 * 60 * 60 
                ]},
                { "$mod": [
                    { "$add": [ 
                        { "$subtract": [ "$date", new Date(0) ] },
                        offset * 1000 * 60 * 60
                    ]},
                    1000 * 60 * 60 * 24
                ]}
            ]
        },
        "count": { "$sum": 1 }
   }}

Which then takes the "offset" for the locale in consideration when reporting back the "dates" to the perpective of the client that was viewing the data. So anything that occurred on an "Adjusted date" resulting in a different day such as the 31st August would be aggregated into the correct grouping by this adjustment.

Because your data may very well be used from the perpective of people in different timezones is exactly the reason why you should keep data for dates in UTC format. The client will do the work, or you can adjust accordingly where needed.

In short:

Client: Construct in local time, send in UTC
Server: Provide TZ Offset and adjust from UTC to local on return

Leave your dates in the correct format they are already in and use the methods described here to report on them.

But if you made a mistake

If however you made a mistake in contruction of your data and all times are actually "local" times but repesented as UTC, ie:

ISODate("2015-08-01T11:10:43.569Z") // actually meant to be 11am in UTC+10 :(

Where it should be:

ISODate("2015-08-01T01:10:43.569Z") // This is 11am UTC+10 :)

Then you would correct this as follows:

var offset = 10,
    bulk = db.collection.initializeUnorderedBulkOp(),
    count = 0;

db.collection.find().forEach(doc) {
    bulk.find({ "_id": doc._id }).updateOne({
       "$set": { "date": new Date(
           doc.date.valueOf() - ( 1000 * 60 * 60 * offset )
       ) }
    });
    count++;

    if ( count % 1000 == 0 ) {
        bulk.execute();
        bulk = db.collection.initializeUnorderedBulkOp();
    }
});

if ( count % 1000 !=0 )
    bulk.execute();

Reading each document to get the "date" value and adjusting that accordingly and sending the updated date value back to the document.

score 3 · Answer 2 · edited May 23 '17 at 11:58

By default MongoDB stores all DateTimes as UTC.

There are 2 ways of doing this:

App side (Recommended)

When extracting the start and end from the database, in your language of choice just change it from a UTC to a local datetime.

To have a look at a good example in Python, check out this answer

Database side (Not recommended)

The other option is to write a mongodb query which adds 8 hours on to your start and end like you originally wanted. However this then sets the time as UTC but 8 hours in the future and becomes illogical for other developers and when parsing app side.

This requires updating based on another value in your document so you'll have to loop through each document as described here.

How to update collection and increment hours for ISO date

2 Answers2

But if you made a mistake

Linked