CODEWITHSUNDEEP
mongodbdatetimestampmongodb-query
NGabioud
NGabioud

Reputation: 375

Find() using timestamp

I'm trying to query a MongoDB collection to find an alarm between two dates. The problem is that the date is a timestamp. My code is:

rs.slaveOk();
print("mac;inicio;fin;valor_inicial;valor_actual");
db.alarmas.find(
{   
   timestamp_inicio_alarma:
        {
            $gt: ISODate("2018-02-01T00:00:00.000Z"),
            $lt: ISODate("2018-02-28T23:59:59.000Z")
        },
        valor_inicial: {$eq: 'ON: AC Line Fail'},
        estado_alarma: {$eq: 'SOLUCIONADA'}
        }
   ).forEach(function(alarmas){
        print(alarmas.mac+";"+alarmas.timestamp_inicio_alarma+";"+alarmas.timestamp_fin_alarma+";"+alarmas.valor_inicial+";"+alarmas.valor_actual);
   }
);

The data format is:

enter image description here

The problem is it's not finding anything. How can I query right?

Thank you very much. MongoDB it's a tool for me, I'm not a programmer.

Upvotes: 2

Views: 2811

Answers (2)

Cristhian D
Cristhian D

Reputation: 692

If you need to find any document by numeric field (timestamp) you can pass this number to Date

db.alarmas.find({
  timestamp_inicio_alarma: { 
    $gt: new Date(start),
    $lt: new Date(end) 
  }
})

Where start and end are timestamp example: 1601949824094

Upvotes: 0

glytching
glytching

Reputation: 47865

According to your screenshot timestamp_inicio_alarma is persisted as Int64 not Date. I'm guessing the value in timestamp_inicio_alarma is the epoch seconds.

So, you are trying to apply a date based query against a document attribute which contains a number not a date. This is why your query is not matching anything.

The following query would match the document shown in your screenshot ...

db.alarmas.find({ 
    timestamp_inicio_alarma: { $gt: 1457029620, $lt: 1457029640}
})

... but, of course, that's difficult to understand (who knows what 1457029620 means, in terms of a date ;).

So, either:

  • Your model changes; to persist timestamp_inicio_alarma as a date. If so, then the way you expressed the query in your question would be valid.

  • Your query changes; to express the dates as seconds since the epoch (assuming that's the vaule in timestamp_inicio_alarma). For example:

    var start = ISODate("2018-02-01T00:00:00.000Z").getTime() / 1000;
var end = ISODate("2018-02-28T23:59:59.000Z").getTime() / 1000;

db.alarmas.find({ 
    timestamp_inicio_alarma: { $gt: start, $lt: end}
})

Upvotes: 1

Related Questions