Add Timestamp while insertMany() in mongoDB

Question

Hi I am MySQL user and new to mongoDB. I get data from my IOT devices as following:

$body={
  "key": "121239",
  "secrete": "Your_Device_Secrete",
  "data": [
    {
      "Temperature":50,
      "Humidity":30,
      "Vibration":100,
      "Time":"2020-1-26 00:00:01"
    },
    {
      "Temperature":55,
      "Humidity":34,
      "Vibration":50,
      "Time":"2020-1-26 00:00:02"
    }
  ]
}

I am inserting it into mongoDB using PHP CodeIgnitor as following:

$this->mongo->batch_insert($body["key"],$body["data"]);

data is getting inserted in collection named as given key as following:

    {
        "Temperature": 50,
        "Humidity": 30,
        "Vibration": 100,
        "Time": "2020-1-26 00:00:01",
        "_id": {
            "$id": "5e330be3f7577f640d2a0922"
        }
    },
    {
        "Temperature": 55,
        "Humidity": 34,
        "Vibration": 50,
        "Time": "2020-1-26 00:00:02",
        "_id": {
            "$id": "5e330be3f7577f640d2a0923"
        }
    }

Now I want to add timestamp to every row getting inserted. I want data getting inserted as following :

{
        "Temperature": 50,
        "Humidity": 30,
        "Vibration": 100,
        "Time": "2020-1-26 00:00:01",
        "_id": {
            "$id": "5e330be3f7577f640d2a0922"
        },
        timestamp:<CURRUNT TIME>
    },
    {
        "Temperature": 55,
        "Humidity": 34,
        "Vibration": 50,
        "Time": "2020-1-26 00:00:02",
        "_id": {
            "$id": "5e330be3f7577f640d2a0923"
        },
        timestamp:<CURRUNT TIME>
    }

is there any way to make mongoDB add current timestamp auto like MySQL?

Answer 1

I also had similar requirements as yours while reading sensor values from DHT11 sensor in my IoT class. I was able to insert all the temperature and humidity readings into MongoDB but later while plotting the graph I realized that I would need the timestamps as well. After researching for a while, I came up with this query. However, it will create a new collection in the database. Here, the new collection name is dht11-sensor-readings.

Please also refer to https://docs.mongodb.com/manual/reference/operator/aggregation/out/ for more.

db.collection.aggregate([
    {
        $addFields:{timestamp:{$toDate:"$_id"}
        }
    }, 
    {   $out:"dht11-sensor-readings"
    }
 ])

Answer 2

First of all you should not store date/time values as string! It will just generate trouble at later times. Better use

"Time": ISODate("2020-01-26T00:00:02Z")

"Time": new Date("2020-01-26 00:00:02") may also work

Then in general you don't need to insert a timestamp. Every document gets an internal _id identifier which contains also the time when it was inserted. You can try like this:

db.collection.aggregate([
  {
    $project: {
      timestamp: {
        $toDate: "$_id"
      },
      key: 1,
      secrete: 1,
      data: 1,
      _id: 0
    }
  }
])

Result:

  {
    "data": [
      {
        "Humidity": 30,
        "Temperature": 50,
        "Time": "2018-02-26 01:00:00",
        "Vibration": 100
      },
      {
        "Humidity": 34,
        "Temperature": 55,
        "Time": "2018-02-26 01:00:00",
        "Vibration": 50
      }
    ],
    "key": "121239",
    "secrete": "Your_Device_Secrete",
    "timestamp": ISODate("2018-02-26T00:00:00Z")
  }

You can also query for it, e.g.

db.collection.find({
  $expr: {
    $gte: [ {"$toDate": "$_id"}, ISODate("2020-01-03T11:00:00Z") ]
  }
})

However, if you like to insert timestamp explicitly then simply do it like this.

{
"Temperature": 50,
"Humidity": 30,
"Vibration": 100,
"_id": {
    "$id": "5e330be3f7577f640d2a0923"
},
"timestamp": new Date()
}

Or "timestamp": ISODate() should also work

Another note, this embedded document "_id": {"$id": "5e330be3f7577f640d2a0923"} looks really strange. I assume this is a bug. The string look like a ObjectId object used for _id. Somehow you scratched it.

Update

I really recommend to use proper data types. In PHP the insert could look like this:

$mongo->db->collection->insertOne([         
    "timestamp" => new MongoDB\BSON\UTCDateTime(NULL),
    "key" => "121239",
    "secrete" => "Your_Device_Secrete",
    "data" => [
        [
        "Temperature" => 50,
        "Humidity" => 30,
        "Vibration" => 100,
        "Time" => new MongoDB\BSON\UTCDateTime(new DateTime("2020-1-26 00:00:01"))
        ],
        [
        "Temperature" => 55,
        "Humidity" => 34,
        "Vibration" => 50,
        "Time" => new MongoDB\BSON\UTCDateTime(new DateTime("2020-1-26 00:00:02"))
        ]
    ]   
]);