Query mongodb datetime output to be for certain timezone

Question

I have a rather basic question. the date time for an entry in a collection is saved as

    "lastUpdated": ISODate("2011-12-07T02:46:51.101Z")

which is in GMT format. How do I query the entry so that the query output i get is in EST format? is this possible in the query itself or do I have to manually subtract 5 hrs (ESt = -5.00 hrs)? The query i used is;

    db.Collection.find({Type: 'Reports', patId: 'JOHNSONGARY'}, 
                       {'lastUpdated': 1} )

EDIT: I use python for querying and am using the returned timestamp as such;

    str(mongo_documents['lastUpdated'].strftime('%Y-%m-%d %H:%M:%S'))

How do i deduct 5 hours in this command?

Answer 1

Check out the documentation - datetime objects returned by pymongo always represent a time in UTC, just as dates stored in MongoDB are always stored as (that is, assumed to be in) UTC

pymongo can convert your datetimes automatically to be time zone aware if you set the tz_info flag to True when creating your Connection. You can then use datetime.astimezone() method to convert to another time zone if you wish.

So for example you could use pytz for timezones or if you only need EST write your own:

import datetime

class Eastern(datetime.tzinfo):

    def utcoffset(self, dt):
      return datetime.timedelta(hours=-5)

    def tzname(self, dt): 
        return "EST"

    def dst(self, dt):
        return datetime.timedelta(0)


EST = Eastern()

Then you can do this:

# Get now for EST
now = datetime.datetime.now(EST)
print now.strftime('%Y-%m-%d %H:%M:%S')

from pymongo import Connection
# Create a timezone aware connection
connection = Connection('localhost', 27017, tz_aware=True)

# Save your data
db = connection.test_database
db.stackoverflow.save({"Type": "reports", "patId": 'JOHNSONGARY', "lastUpdated": now})

doc = db.stackoverflow.find()[0]
print doc['lastUpdated'].astimezone(EST).strftime('%Y-%m-%d %H:%M:%S')

# Confirm they are the same
assert doc['lastUpdated'].astimezone(EST).strftime('%Y-%m-%d %H:%M:%S') == now.strftime('%Y-%m-%d %H:%M:%S')

Answer 2

If you're using C#, you can apply this answer.

If you're using Ruby, then you'll have to subtract dates yourself (or I don't know about such mechanism).

Other languages - have no idea :-)