11

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?

user3702039
  • 133
  • 1
  • 1
  • 9
  • send the format and which format you want mention those – Amaresh Jun 23 '15 at 06:52
  • I am searching for the records on the date 25-06-2015. I have a record A on date 2015-06-24 24:17:51. On querying, this record A has to convert based on timezone and get listed. – user3702039 Jun 23 '15 at 06:57
  • is `timeZone` saved in documents or you passed this externally ? – Neo-coder Jun 23 '15 at 07:18
  • I pass it externally. – user3702039 Jun 23 '15 at 07:51
  • 1
    The BSON date format is timezone agnostic (internally it is a 64-bit integer that represents the number of milliseconds since Jan 1, 1970 00:00:00 UTC). The conversion to a readable format in a specific timezone usually happens on the frontend. – Philipp Jun 23 '15 at 08:03

5 Answers5

11

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"
} }
Saravana
  • 12,647
  • 2
  • 39
  • 57
5

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

Community
  • 1
  • 1
Neo-coder
  • 7,715
  • 4
  • 33
  • 52
0

In case if the dates are not changed and constant e.g. something like created_record_date then whichever timezone data you need it, you should pre-calculate and save (as String) along with the same document so that you don't have to run the huge processing at the runtime which could slow down the execution time. in case you have existing records and you want to store the various different timezone data along with the records, think about running a Map-Reduct job and update the documents separately. (let me know if you need the code for that). However, if this date field can be changed as per the business logic then its wise to calculate at runtime. both techniques have their different use cases and their pros and cons.

-$

Sachin Shukla
  • 173
  • 10
0

If you are using mongoose (probably also works in native driver):

import moment from 'moment-timezone'; // this is needed to use .tz() method
import mongoMoment from 'mongodb-moment';

// Initalize mongodb-moment so you can use moment() object directly in mongo query
mongoMoment(moment);

// Add timezone to your_date
const date = moment(your_date)
        .tz("Europe/Zagreb");

// Make $gte/$lte queries with date ...
TomoMiha
  • 1,218
  • 1
  • 14
  • 12
-1

You can search like below in Compass:

date>16 and date <17 
{createdTimStamp:{$gte:ISODate("2023-03-23T04:00:00Z)}}
t04 is UTC-4

Remember, the current date will always be stored in GMT. Here, I want results on the 23rd of March; since all data is stored in GMT by default, it will assume we are searching in GMT. So if you want to give a time zone while searching, use the above query.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
user21481791
  • 1