How to Convert Timestamp Into Date in MongoDB
- Convert Timestamp Into Date in MongoDB
- Convert Timestamp Into Date When Timestamp Is of Type Number
- Convert Timestamp Into Date When Timestamp Is of Type String
- Convert Timestamp Into Date When Timestamp Is of Type Object
This tutorial demonstrates how we can convert the timestamp into date in MongoDB. It also illustrates how to count the entries for a specific date.
Convert Timestamp Into Date in MongoDB
Converting from timestamp into date depends on what type we have saved the timestamp. Is it of the type object, number, or string?
We can check the type of field using the following command on the mongo shell. In this tutorial, we will learn how to convert the timestamp to date if it is of type number, string, or object.
Check Type of the Field:
// MongoDB 5.0.8
> typeof db.collection_name.findOne().fieldName;
Convert Timestamp Into Date When Timestamp Is of Type Number
Example 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 Type of the datetime Field:
// MongoDB 5.0.8
> typeof db.collection1.findOne().datetime;
OUTPUT:
number
Once the collection is ready and we know the field type, we can use the following approach to convert from timestamp to date and count the entries per date.
Example 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 the $project aggregation stage that takes the documents from the specified collection and tells the fields’ inclusion, _id field’s suppression, new fields’ addition, and resetting the existing fields’ values.
Inside the $project stage, we convert the value of the datetime field to date using the $toDate aggregation and save it in the _id field, which is further passed to the $group aggregation stage.
At this stage, we use the $dateToString aggregation pipeline operator to convert the specified date object to string according to the specified format and save it in the _id field, which is further used to group the documents.
The $dateToString takes either timestamp, date, or ObjectId that is further transformed considering the user-specified format, while $sum returns the collective sum of the numeric values only.
Finally, we group the documents by the item, which is _id here. Remember that the _id now contains a string value because we converted the specified date to a string per user-specified format.
Convert Timestamp Into Date When Timestamp Is of Type String
Example 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 Type of the datetime Field:
// MongoDB 5.0.8
> typeof db.collection2.findOne().datetime;
OUTPUT:
string
In this collection, we have the timestamp in the string format. So, we can use the following solution to convert it from timestamp to date and group them as per date.
Example 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 except for one difference. Here, we are using $toLong to convert the datetime field from string to number type first, and then use that converted value to transform to date using $toDate.
Convert Timestamp Into Date When Timestamp Is of Type Object
Example 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 Type of the datetime Field:
// MongoDB 5.0.8
> typeof db.collection3.findOne().datetime;
OUTPUT:
object
This time we can use the following solution to convert timestamp to date and count the entries per date.
Example 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 }
