Uploaded image for project: 'Documentation'
  1. Documentation
  2. DOCS-3576

Provide examples for timezone offsets in aggregation

    XMLWordPrintableJSON

Details

    • Icon: Task Task
    • Resolution: Incomplete
    • Icon: Major - P3 Major - P3
    • v1.3.13
    • None
    • manual

    Description

      Currently we have an open feature SERVER-6310 to provide timezone support within aggregation operations.

      Until this feature is complete end users can work with timezones by using the $add operator. We should document how this is done for end users.

      Consider the following documents:

      db.bar.insert({ "st" : ISODate("2014-06-11T23:00:00Z"), tz: -5 })
      db.bar.insert({ "st" : ISODate("2014-06-12T00:00:00Z"), tz: -5 })
      db.bar.insert({ "st" : ISODate("2014-06-12T01:00:00Z"), tz: -5 })
      db.bar.insert({ "st" : ISODate("2014-06-12T02:00:00Z"), tz: -5 })

      We can aggregate these to display in EST with the following projection.

      db.bar.aggregate(
          [ {$project: { _id: 1, st: {$add : ["$st", 3600000 * -5 ]}}},
          {$group : { '_id' : {$dayOfMonth : "$st"}, 'count' : { $sum : 1 } }} ])

      Result:

      { "_id" : 11, "count" : 4 }

      You could also be slightly more ambitious and store the date in UTC and a Timezone offset in the document with it.

      db.bar.aggregate(
          [ {$project: { _id: 1, st: {$add : ["$st", 3600000 * "$tz" ]}}},
          {$group : { '_id' : {$dayOfMonth : "$st"}, 'count' : { $sum : 1 } }} ])

      Result:

      { "_id" : { "dy" : 11 }, "count" : 4 }

      Attachments

        Activity

          People

            kay.kim@mongodb.com Kay Kim (Inactive)
            david.hows David Hows
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              9 years, 15 weeks ago