CODEWITHSUNDEEP
mongodbdatemilliseconds
Mark
Mark

Reputation: 1031

Filter dates in milliseconds

Hello i have a DB (MongoDB) with many entries with a date in milliseconds. I want to extract all the entries that have a date between 6:00 and 10:00 in the morning How can i do it? Is it possible to do it in a single query? Something like this extract all the entries before Tue Jul 17 2012 14:09:05 for example

db.OBSERVABLEPARAMETER.find({startDate:{$lte:1342526945150}})

Upvotes: 1

Views: 1897

Answers (2)

Mark
Mark

Reputation: 1031

i wasn't able to build the query with javascript, i solved with a php scrip on a webpage that query the MongoDB in this manner (this function calculates the average hour an object with fieldname "glicemia" is inserted at):

public function count_glicemie_momento_media($momento){
        try{
            // access collection
            $collection = $this->db->OBSERVABLEPARAMETER;
            // execute query
            // retrieve all documents
            $cursor = $collection->find(array( "parameter_name" => "glicemia","measuredValue2"=>$momento, "status" => array('$ne' => "DELETE")));
            $sum=0;
            foreach($cursor as $glicemia){
            $sum+=date("G", $glicemia["startDate"]/1000);
            }
            return round($sum/$cursor->count(),2);
        } catch (MongoConnectionException $e) {
          die('Error connecting to MongoDB server');
        } catch (MongoException $e) {
          die('Error: ' . $e->getMessage());
        }
        return -1;
    }

Upvotes: 0

Philipp
Philipp

Reputation: 69663

Analogue to the $lte-operator there is also the $gte (greater-than-or-equal) operator. Both can be combined in the same object:

db.OBSERVABLEPARAMETER.find({startDate:{$gte:1342560000000, $lte:1342570000000}})

(values aren't specific timestamps, they are just to illustrate the concept)

This allows you to get all data in a specific timeframe. But when you want to have all data within a specific time period on any day, it gets a lot more complicated, both for you and for the database. Such a complex query requires a $where operator with a javascript function which extracts the hours from the timestamp and returns true when they are between 6 and 10.

By the way: The recommended way to store dates in MongoDB is using the Date type. Using integer timestamps is discouraged. See http://docs.mongodb.org/manual/core/document/#document-bson-type-considerations

Upvotes: 1

Related Questions