Timezone in mongo query

Question

I have data inserted in UTC time format in mongodb. I want timings to be converted based on the timezone. Is there any possibility to do so in mongo query?

Answer 1

In mongo version 3.6 timezone has been added, mongo doc

expression to extract date part with timezone is

{ date: <dateExpression>, timezone: <tzExpression> }

we can either specify the timezone or offset while getting the date parts.

see my answer posted here

to get date from date with timezone America/Chicago

{ $month: {
    date: new Date(),
    timezone: "America/Chicago"
} }

or with offset

{ $month: {
    date: ISODate(),
    timezone: "-0500"
} }

Answer 2

Let consider your document contains ISODate as below :

db.collection.insert({"date":new Date()})

Above query insert date in ISODate format now you want to convert this ISODate into give timeZone.

Suppose you want to convert above date to Eastern Daylight Saving Time ( EDT ) epoch time zone conertor then offset converted as 14400 * 1000. First convert ISODate to timeStamp and then use substract EDT OffsetintimeStampand then converttimeStamptoISODate` again.

Check below aggregation query :

db.collection.aggregate({
  "$project": {
    "timestamp": { //convert ISODate tom timestamp
      "$subtract": [{
        "$divide": [{
          "$subtract": ["$date", new Date("1970-01-01")]
        }, 1000]
      }, {
        "$mod": [{
          "$divide": [{
            "$subtract": ["$date", new Date("1970-01-01")]
          }, 1000]
        }, 1]
      }]
    }
  }
}, {
  "$project": {
    "timeZoneTimeStamp": {
      "$subtract": [{ //substract timestamp to given offset if offset will in postive then replace  subtract  to add
        "$multiply": ["$timestamp", 1000]
      }, 14400000]
    }
  }
}, {
  "$project": {
    "timeZoneTimeStamp": 1, //converted timeZoneTimeStamp if required 
    "_id": 0,
    "newDate": { // newDate is converted timezone ISODate
      "$add": [new Date(0), "$timeZoneTimeStamp"]
    }
  }
})

NOTE : In above query conversion from ISODATE to timeStamp ref. here