CODEWITHSUNDEEP
javascriptnode.jsmongodbdate
Daniel Scocco
Daniel Scocco

Reputation: 7266

Mongodb timezone issue when filtering by date

I am trying to get all records with "date" field between startDate and endDate.

I am querying the DB with this (to get records from beginning of the month until today):

 var startDate = new Date(2017,09,1); 
    var endDate = new Date(2017,09,7); 

//console.log(startDate) = Sun Oct 01 2017 00:00:00 GMT-0300 (BRT)
//console.log(endDate) = Sat Oct 07 2017 00:00:00 GMT-0300 (BRT)

    Sale.find({date:{"$gte":startDate,"$lt":endDate}},function(err, sales){

    }

Records with dates between 10/02 and 10/07 and being returned, but records with date 10/01 are not being returned. Here is the date field of those missing records:

 "date": {
        "$date": "2017-10-01T23:00:00.000Z"
    }

I set the hour to 23 to see if it would make a difference (timezone,etc) but it didn't.

If I set the startDate to September 30 hour 23, nothing. Hour 22, 21, nothing. When startDate becomes September 30, hour 20, bingo, the records are returned.

This is probably a timezone issue, but I can't identify where and how.

Upvotes: 3

Views: 1235

Answers (1)

c-smile
c-smile

Reputation: 27460

This 2017-10-01T23:00:00.000Z is GMT time. This new Date(2017,10,01); is your local time and does not equal to the above as soon as you are not sitting on Greenwich meridian.

JS stores dates in GMT seconds since some base date.

So in first case number of seconds is X and in second case it is X + Date.getTimezoneOffset().

You can use this function:

function ZDate(year,month,day) {
  return new Date(year + "-" + month + "-" + day);
}

Or as RobG suggested:

function ZDate(year,month,day) {
  return Date.UTC(year, month-1, day); 
}

here:

var startDate = ZDate(2017,09,1); 
var endDate = ZDate(2017,09,7); `

Upvotes: 3

Related Questions