acbh acbh - 2 months ago 40x
JSON Question

What format should I use to store timestamp in JSON for mongodb queries used in nodejs?

I am currently using python to preprocess my data into JSON and insert my data into the collection via nodejs:

collection.insert(data, {safe:true}, function(err, result) {});
My queries will be executed using nodejs as well.

I previously used python
to format my timestamps, and this is how my JSON is structured:

"timestamp" : "2016-08-02 20:30:02",
"detail" : blah blah blah
"timestamp" : "2016-08-02 20:33:23",
"detail" : blah blah blah

I was trying to query based on the timestamp, and according to a lot of posts such as this Create an ISODate with pyMongo, I am supposed to use
for my query. So one of my attempts is:

collection.findOne({ timestamp: new ISODate("2016-08-02T20:33:23.786Z")})

This one says that ISODate is undefined. So based on this post I changed my query to:

collection.findOne({ timestamp: new Date("2016-08-02T20:33:23.786Z")})

This one says record not found. How should I store my timestamps into JSON? Is
new Date("2016-08-02T20:33:23.786Z")
the right way to retrieve my instance? Also, when mongodb query timestamps, will it look at exact hours and minutes as well? I want to find the exact year, month, day, hour, minute and second just like how my timestamp is stored.


The following answer suggests that the best way is to use the JavaScript Date (constructed also via ISODate), which is native to Mongo. Digging into the BSON documentation it further clarifies the internal values:

BSON Date is a 64-bit integer that represents the number of milliseconds since the Unix epoch (Jan 1, 1970). This results in a representable date range of about 290 million years into the past and future.

So the short answer is: no matter which language you store it from, just store that 64bit integer number of milliseconds, which you will be able to read natively. I would expect that value to be in UTC, both written and read.

The following answer also tells you how to convert your python datetime object to milliseconds since epoch.