Calculating and comparing unixtime offsets

Working with Data
,
1

Hello,

I fear as newbie I’m missing something really obvious but no amount of googling, including searching the forum is helping… hence why I think I might be barking up the wrong tree.

I’m ‘simply’ trying to find documents where a field called timestamp (I now know that was a bad name) is later/newer than a calculated unixtime. My documents all follow this format:

{
    "objectClass": "Trunks",
    "timestamp": "1601590491",
    "Trunk": "Trunk_External",
    "Calls": 0,
    "Call Rate": 0,
    ...  blah blah...
  }

I’m trying to do something like this:

db.collection.find({
     timestamp: { $gt: (new Date()-12345).getTime() }
})

Obviously the ‘12345’ could be any number of seconds/milliseconds and then I’d get all the documents returned with a “timestamp” after that calculated date/time.

I’ve tried so many things I’m not sure if it is syntax error or non-existent function. I’m not even sure getTime() is allowed in a MongoDB query, but after several hours I must seek help before time runs out and I look at extracting the whole dataset and then parsing it outside MongoDB!

Has anyone tried using and comparing unixtimes in MongoDB, or is it really just a very bad date format to try and use? Cheers!!

2

Hello @Chunky_Plumpy! welcome to the community.

“timestamp”: “1601590491”,

This is of type Epoch, also known as Unix timestamps, is the number of seconds (NOT milliseconds) that have elapsed since January 1, 1970 at 00:00:00 GMT.

In MongoDB, the Date objects are stored as a signed 64-bit integer representing the number of milliseconds since the Unix epoch (Jan 1, 1970).

Then, there are 1000 milliseconds in a second.

3

Hello @Prasad_Saya, thank you for stepping in to assist. You are correct, this was one of my many mistakes but the thing that caught me out in the end was timezones. Despite the version posted originally being wrong I was able to filter correctly using:

…find ({timestamp:{$gt:new Date().getTime()/1000-3600}})

Sadly in the first hour (important timespan) it simply returned nothing so I charged off trying a multitude of other things and confusing myself. If only to prevent another newbie making the same mistake, the source system was generating BST Epoc timestamps which were actually ‘the future’. By the time Epoc had overtaken my original test data timestamps I’d gotten into a right old mess :smiley:

All sorted now, and thanks again as the ‘1000’ part was equally important!! Cheers.

1 Like