5

Is there a way to take timezone into account when running a mongo query? We have an issue whereby since the clocks have gone forward (to BST) our mongo aggregation query does not group records as expected.

For example, say I have 3 records with a createdDate of 2017-03-27 13:00:00, 2017-03-28 00:30:00, and 2017-03-28 13:00:00 I would expect the aggregation to group 1 record for 27/3 and 2 records for 28/3. However, because the second record is stored in the database in UTC as 2017-03-27 23:30:00 the aggregation groups 2 records for 27/3 and only 1 for 28/3.

This is the aggregation query:

{ "$match" : { "$and" : [ 
    { "createdDate" : { "$gte" : { "$date" : {ISODATE}} , 
                        "$lte" : { "$date" : {ISODATE}}}
]}},
{ "$group" : { "_id" : { "_id" : "$id" , 
    "createdDate" : { 
       "year" : { "$year" : "$createdDate"} , 
       "month" : { "$month" : "$createdDate"} , 
       "day" : { "$dayOfMonth" : "$createdDate"}}} , 
    "count" : { "$sum" : 1}
}}

Any ideas how to get around this issue?

maloney
  • 1,633
  • 3
  • 26
  • 49
  • Yes, and that is the expected result as the three fields are stored as milliseconds since the epoch in the database, the suggestion here is to add a key in your collection for the time zone: https://docs.mongodb.com/v3.2/tutorial/model-time-data/ – Euclides Mar 30 '17 at 15:18
  • @Euclides can you apply the offset to each date before it tries to `group`? Just want to make sure it will work if the query is run across the clock change period i.e. you are potentially reporting on 2 different timezones within the same query. – maloney Mar 30 '17 at 15:57
  • Yes, I found this post http://stackoverflow.com/questions/31353740/aggregating-in-local-timezone-in-mongodb/31354088#31354088 Let me know if it is helpful or we might see other options. – Euclides Mar 30 '17 at 18:18

1 Answers1

0
To get the date according to user requirement get the offset from frontend

let say it as tzOffset->

var tzOffset = 2;

then use in aggregate this way to get user result

{"$add": [{ "$subtract": [ "$date", new Date("1970-01-01") ] },tzOffset * 1000 * 60 * 60]}

then do the comparison.
Ishan Garg
  • 571
  • 5
  • 13