Timezone query on MongoDB

Question

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:

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

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

It's because your "at" field is a string field and not an ISODate field. You would need to convert them to the proper datatype. — kevinadi, Feb 12 '20 at 05:17
Did that, and now I'm having to query a bit differently ```{ "at": { "$gte": ISODate("2019-09-29T00:00:00+00:00"), "$lte": ISODate("2019-09-30T00:00:00+00:00") } }```, but to no avail — Edward Leoni, Feb 12 '20 at 08:19

score 2 · Accepted Answer · answered Feb 13 '20 at 02:28

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.

Timezone query on MongoDB

1 Answers1