[DOCS-3576] Provide examples for timezone offsets in aggregation Created: 12/Jun/14  Updated: 16/Mar/15  Resolved: 05/Nov/14

Status: Closed
Project: Documentation
Component/s: manual
Affects Version/s: None
Fix Version/s: v1.3.13

Type: Task Priority: Major - P3
Reporter: David Hows Assignee: Kay Kim (Inactive)
Resolution: Incomplete Votes: 0
Labels: docs-cookbook
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Participants:
Days since reply: 9 years, 15 weeks ago

 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 }



 Comments   
Comment by Kay Kim (Inactive) [ 05/Nov/14 ]

will merge into cookbook

Comment by Kay Kim (Inactive) [ 05/Nov/14 ]

writing-1065

Generated at Thu Feb 08 07:46:02 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.