1

Im struggling with localized datetimes. All the dates stored in mongo are converted to UTC automatically, so we have to localized them after retrieving them. Im fine with that, but..

In the case where I make a query to group records by date, meaning YYY-MM-DD, problems arise. Since my local time is GMT-3, any record with time above 21:00 will be stored in mongo as 00:00, thus corresponding to the following day. When grouping by date in the query i'd be getting records in the wrong day, and wont be able to recover from that because i lose the hour details.

Is there a way of localizing the dates in the groupby command in a pymongo query?

Here's the code:

def records_by_date():
    pipeline = []
    pipeline.append({"$group": {
        "_id": {
            "$concat": [
                {"$substr": [{"$year": "$date"}, 0, 4]},
                "-",
                {"$substr": [{"$month": "$date"}, 0, 2]},
                "-",
                {"$substr": [{"$dayOfMonth": "$date"}, 0, 2]}
            ]},
        "record_id": {"$push": "$_id"},
        "count": {"$sum": 1}
    }})
    pipeline.append({"$project": {
        "_id": 0,
        "date": "$_id",
        "record_id": 1,
        "count": 1
    }})
    pipeline.append({"$sort": {"date": 1}})
    return self.collection.aggregate(pipeline)['result']

If I add the hour details, I could verify the records after that, but then I wouldn't be grouping by date.

Any ideas?

Sebastian
  • 1,243
  • 1
  • 18
  • 34
  • possible duplicate of [How to deal with the timezone issue when storing dates in utc using mongod?](http://stackoverflow.com/questions/18287493/how-to-deal-with-the-timezone-issue-when-storing-dates-in-utc-using-mongod) – shx2 Apr 20 '14 at 05:13
  • 1
    @shx2 you are right yes they provide a work around for this issue. Final answer would be that mongo doesnt provide a solution for that, but you can always use this trick.thanks! – Sebastian Apr 20 '14 at 16:11

0 Answers0