JIYIK CN >

This article demonstrates how to convert a timestamp to a date in MongoDB. It also explains how to count entries for a specific date.

Convert Timestamp to Date in MongoDB

Converting from timestamp to date depends on the type we are saving the timestamp in. Is it an object, a number or a string?

We can check the type of the field using the following command on the mongo shell. In this tutorial, we will learn how to convert a timestamp to a date of type number, string, or object.

Check the field type:

// MongoDB 5.0.8

> typeof db.collection_name.findOne().fieldName;

When the timestamp is a numeric type, convert the timestamp to a date

Sample code (for collection1):

// MongoDB 5.0.8

> db.collection1.insertMany([
    {"_id": 1, "datetime": new Date().getTime()}, //saves timestamp in milliseconds
    {"_id": 2, "datetime": new Date().getTime()},
    {"_id": 3, "datetime": new Date().getTime()},
    {"_id": 4, "datetime": new Date().getTime()},
    {"_id": 5, "datetime": new Date().getTime()}
]);

> db.collection1.find();

Output:

{ "_id" : 1, "datetime" : 1655448286502 }
{ "_id" : 2, "datetime" : 1655448286502 }
{ "_id" : 3, "datetime" : 1655448286502 }
{ "_id" : 4, "datetime" : 1655448286502 }
{ "_id" : 5, "datetime" : 1655448286502 }

Check datetimethe type of the field:

// MongoDB 5.0.8

> typeof db.collection1.findOne().datetime;

Output:

number

Once the collection is ready and we know the field types, we can use the following method to convert the timestamp to date and count the entries for each date.

Sample code (for collection1):

// MongoDB 5.0.8

> db.collection1.aggregate([
      {
          "$project": {
              "_id": { "$toDate": "$datetime" }
           }
      },
      {
          "$group": {
              "_id": { "$dateToString": { "format": "%Y-%m-%d", "date": "$_id" }},
              "count": { "$sum": 1 }
          }
      }
]);

Output:

{ "_id" : "2022-06-17", "count" : 5 }

Here, we use $projectthe aggregation stage, which fetches documents from a specified collection and tells the inclusion of fields, _idsuppression of fields, addition of new fields, and resetting the values ​​of existing fields.

In $projectthe stage, we use $toDatethe aggregation to datetimeconvert the value of the field into a date and save it in _idthe field, which is further passed to $groupthe aggregation stage.

At this stage, we use $dateToStringthe aggregation pipeline operator to convert the specified dateobject into a string as per the specified format and save it in _idthe field, which is further used to group the documents.

$dateToStringUses timestamp, date, or ObjectId, performs further conversion according to the user-specified format, and $sumreturns only the sum of the values.

Finally, we group the documents by project, here it is _id. Remember that _idnow contains a string value because we converted the specified date to a string according to the format specified by the user.

When the timestamp is a string type, convert the timestamp to a date

Sample code (for collection2):

// MongoDB 5.0.8

> db.collection2.insertMany([
    {"_id": 1, "datetime": "1655445247168"},
    {"_id": 2, "datetime": "1522838153324"},
    {"_id": 3, "datetime": "1513421466415"},
    {"_id": 4, "datetime": "1515488183153"},
    {"_id": 5, "datetime": "1521571234500"}
]);

> db.collection2.find();

Output:

{ "_id" : 1, "datetime" : "1655445247168" }
{ "_id" : 2, "datetime" : "1522838153324" }
{ "_id" : 3, "datetime" : "1513421466415" }
{ "_id" : 4, "datetime" : "1515488183153" }
{ "_id" : 5, "datetime" : "1521571234500" }

Check datetimethe type of the field:

// MongoDB 5.0.8

> typeof db.collection2.findOne().datetime;

Output:

string

In this collection, we have timestamp in string format. So, we can use the following solution to convert it from timestamp to date and group them by date.

Sample code (for collection2):

// MongoDB 5.0.8

> db.collection2.aggregate([
      {
          "$project": {
              "_id": { "$toDate": { "$toLong": "$datetime" }}
          }
      },
      {
          "$group": {
              "_id": { "$dateToString": { "format": "%Y-%m-%d", "date": "$_id" } },
              "count": { "$sum": 1 }
          }
      }
]);

Output:

{ "_id" : "2018-03-20", "count" : 1 }
{ "_id" : "2017-12-16", "count" : 1 }
{ "_id" : "2022-06-17", "count" : 1 }
{ "_id" : "2018-04-04", "count" : 1 }
{ "_id" : "2018-01-09", "count" : 1 }

This code is the same as the previous example, but with one difference. Here, we first convert the field from a string type to a numeric type using $toLong, datetimeand then use the converted value $toDateto convert to a date using .

When the timestamp is an object type, convert the timestamp to a date

Sample code (for collection3):

// MongoDB 5.0.8

> db.collection3.insertMany([
    {"_id":1, "datetime": new Timestamp()},
    {"_id":2, "datetime": new Timestamp()},
    {"_id":3, "datetime": new Timestamp()},
    {"_id":4, "datetime": new Timestamp()},
    {"_id":5, "datetime": new Timestamp()}
]);

> db.collection3.find();

Output:

{ "_id" : 1, "datetime" : Timestamp(1655448393, 1) }
{ "_id" : 2, "datetime" : Timestamp(1655448393, 2) }
{ "_id" : 3, "datetime" : Timestamp(1655448393, 3) }
{ "_id" : 4, "datetime" : Timestamp(1655448393, 4) }
{ "_id" : 5, "datetime" : Timestamp(1655448393, 5) }

Check datetimethe type of the field:

// MongoDB 5.0.8

> typeof db.collection3.findOne().datetime;

Output:

object

This time we can use the following solution to convert the timestamp to date and count the entries for each date.

Sample code (for collection3):

// MongoDB 5.0.8

> db.collection3.aggregate([
      {
          "$project": { "_id": "$datetime" }
      },
      {
          "$group": {
              "_id": { "$dateToString": { "format": "%Y-%m-%d", "date": "$_id" } },
              "count": { "$sum": 1 }
          }
      }
]);

Output:

{ "_id" : "2022-06-17", "count" : 5 }