CODEWITHSUNDEEP
mongodbmongodb-querypymongo
Edward Leoni
Edward Leoni

Reputation: 83

Timezone query on MongoDB

I'm building an application wherein users can be located in various timezones, and the queries I run are sensitive to their timezones.

The problem I'm having is that MongoDB seems to be ignoring the timezone on query time!

This is an example of a date field "2019-09-29T23:52:13.495000+13:00", here's the full json: enter image description here

And this is an example of a query:

{
   "at": {
      "$gte": "2019-09-29T00:00:00+00:00",
      "$lte": "2019-09-30T00:00:00+00:00"
   }
}

All of my dates are saved either with +12 or +13 because those are the timezones where my customers are at the moment, essentially what that means for the above query is that I should be seeing some results from the 2019-10-01:00:00:00+13:00 due to the first of October still being the 30th of September in UTC, and I'm not.

I'm new to this and not too far down the rabbit hole so I'm open to refactoring/change of thinking if it will make things easier.

For context, in case it makes a difference I'm using PyMongo, and my MongoDB version is 4.2

EDIT! I've tried converting the "at" field into date, but the timezone seem to have been suppressed or is not visible enter image description here

With that I also had to change the way I query

{ "at": { 
    "$gte": ISODate("2019-09-29T00:00:00+00:00"), 
    "$lte": ISODate("2019-09-30T00:00:00+00:00") 
   } 
}

Didn't help

Upvotes: 4

Views: 4248

Answers (1)

kevinadi
kevinadi

Reputation: 13765

MongoDB uses the UTC timezone when it stores date. Using your example date:

> ISODate("2019-09-29T23:52:13.495000+13:00")
ISODate("2019-09-29T10:52:13.495Z")

So the +13:00 timezone is converted to Z (UTC) timezone by ISODate(), and this is the value stored in the database.

> db.test.insert({_id:0, at: ISODate("2019-09-29T23:52:13.495000+13:00")})

> db.test.find()
{ "_id" : 0, "at" : ISODate("2019-09-29T10:52:13.495Z") }

Note that "at" is stored in UTC time instead of +13 as inserted.

Querying using ISODate() as per your example works as expected:

> db.test.find({ "at": {
...     "$gte": ISODate("2019-09-29T00:00:00+00:00"),
...     "$lte": ISODate("2019-09-30T00:00:00+00:00")
...    }
... })
{ "_id" : 0, "at" : ISODate("2019-09-29T10:52:13.495Z") }

The timezone information is not visible due to the visual tool you're using. It is visible in the mongo shell as Z, as in the above example.

The advantage of storing UTC is that you can convert them to any other timezones without worrying about daylight saving time. There are date specific aggregation operators that deals with timezones and convert between them. See Date Expression Operators and https://stackoverflow.com/a/48535842/5619724 for examples.

Upvotes: 3

Related Questions