How to compare time in MongoDB with different time stamp and format?

Question

I have the dataset looks as following, the time is record in timezone GMT:

{"id":1,"Timestamp:"Mon, 11 May 2015 07:57:46 GMT"}
{"id":2,"Timestamp:"Mon, 11 May 2015 08:57:46 GMT"}

I would like to count how many rows for a certain time period:

from ISODate("2015-05-10T01:40:08.963Z")
to ISODate("2015-05-12T01:40:08.963Z")

Is it possible to do this?

Do you have your timestamp stored as strings? Like in first example? — Salvador Dali, May 12 '15 at 02:37

score 0 · Accepted Answer · edited May 23 '17 at 11:51

0

There is no normal way of doing it. It is better to convert your database to a normal format and the faster you will do it to better.

What I would do is to use foreach for every document and modify it with the following javascript function.

edited May 23 '17 at 11:51

Community

1
1

answered May 12 '15 at 05:19

Salvador Dali

214,103
147
703
753

score 0 · Answer 2 · answered Jan 16 '19 at 05:43

There is a very simple solution to your challenge. Both Data formats (GMT and ISO FORMAT) can be passed easily.

Just follow the function below: function findRows (){

//Convert the dates to strings
const isoDateInUTC1 = new Date("2015-05-12T01:40:08.963Z").getTime(),
isoDateInUTC2 = new Date("2015-05-12T01:40:08.963Z").getTime(),
rows= await collection.find({}).lean();

Let rowCount = 0;

for (let count = 0; count < trades.length; count++) {
   var gmtDateUTC = new Date("").getTime(rows[count].Timestamp);
    isoDateInUTC1 < gmtDateUTC < isoDateInUTC2  
    ?  rowCount++;
 }
return rowCount; 
}

I think this should work, although you should still be able to take the isoDateInUTC1 and isoDateInUTC2 as parameters so you can reuse the function. More so, You should be able to run a similar code in the mongodb query but this should still do the job.

How to compare time in MongoDB with different time stamp and format?

2 Answers2